2

I have the tried the following queries to return a result set back to excel utilizing ADO.

MAX

SELECT DISTINCT Term
FROM uSubjectivities 
WHERE account_no = '1172014'
    AND version_num = (SELECT max(Cast(version_num as Int)) from uSubjectivities 
                            WHERE account_no='1172014' 
                                AND SubjectivityID = '6472140') 
        AND SubjectivityID = '6472140'
        AND TermType = 'Common'

TOP (1)

SELECT DISTINCT Term
FROM uSubjectivities 
WHERE account_no = '1172014'
    AND version_num = (SELECT TOP (1) Cast(version_num as Int)
                        FROM uSubjectivities 
                        WHERE account_no='1172014' 
                            AND SubjectivityID = '6472140'
                        ORDER BY version_num DESC) 
    AND SubjectivityID = '6472140'
    AND TermType = 'Common'

UPDATE JOIN

SELECT DISTINCT Term
FROM uSubjectivities S
INNER JOIN 
(
    SELECT TOP (1) Cast(version_num as Int) v
    FROM uSubjectivities 
    WHERE account_no='1172014' 
        And SubjectivityID = '6472140'
    ORDER BY version_num DESC
) mv
ON mv.v = s.version_num
WHERE SubjectivityID = '6472140'
    AND TermType = 'Common'

However, both are taking a lot longer than I (and my users) would like.

Max takes 14 secs to return 15 records (type is varchar(max) as these can be fields with long text strings). Top 1 takes 14 seconds. Join takes 16 seconds

Any parameter inside single quotes is ultimately passed into the query. I run the query about 6 times (but could be more or less depending on parameters). In my example now, this portion of the code is taking 42 seconds on Max and 37 on Top 1. 40 secs for Join.

Is there anyway to optimize this speed. For now, we are trying to avoid indexing since the database will have to be reindexed over and over down-the-road.

Furthermore, both of those queries run in nanoseconds inside SQLServer so I don't know why they are so slow in ADO.

EDIT I have also loaded this into a Stored Procedure and called as same from VBA. Did not help with return time.

Community
  • 1
  • 1
Scott Holtzman
  • 27,099
  • 5
  • 37
  • 72
  • Perhaps storing the queries inside of SS will speed up your processing since as stored SS objects, they'll be optimized - just pass in the parameters as needed. – Bill Roberts May 22 '17 at 15:22
  • Also, be sure you are you the "best" driver to retrieve your data. If possible, use native SQL SERVER drivers rather than the generic ODBC drivers. – Bill Roberts May 22 '17 at 15:23
  • Oh yeah one other thing... about that nanoseconds result in SS... be sure in MGMT STUDIO that the "full" result set has returned in nanoseconds. Quite possible your result window, even though it looks like you have your results, may still be filling. – Bill Roberts May 22 '17 at 15:26
  • @flaZer - are you saying store the procedure inside SQLServer itself and just call it from the ADO with the parameters? (Also, I am using native SQLServer drivers and getting full result set). – Scott Holtzman May 22 '17 at 15:46
  • Rather than a stored proc, store it as a view inside of SS, which makes more sense since per your examples, they "select" (i.e., not performing inserts, updates, deletes, etc.). Then again, stored procs take parms..., dont believe a view can. – Bill Roberts May 22 '17 at 16:07
  • "For now, we are trying to avoid indexing since the database will have to be reindexed over and over down-the-road." I don't understand. Can you clarify? Without indexes, my expectations would be extremely low. – Dave Mason May 22 '17 at 16:59
  • @flaZer - I think i need a Function or SP since I need to pass parameters for account, id, term. – Scott Holtzman May 22 '17 at 17:20
  • @DMason - My colleague (much more versed in architecture than I) stated that we can index now, but once data tables grow bigger and bigger we will have to keep reindexing every so often. He just stated that we should try to fix it with good query structure before taking Index route. – Scott Holtzman May 22 '17 at 20:27
  • Scott, I will assume your colleague is referring to `ALTER INDEX REORGANIZE` and/or `ALTER INDEX REBUILD`, Yes, those commands are part of a sound index maintenance architecture. And the need for index maintenance becomes more necessary as the table size increases. But to avoid indexes simply because they may require maintenance some day is extremely short-sighted. I simply cannot understand why anyone would choose this as a general practice. – Dave Mason May 22 '17 at 22:13
  • 1
    @DMason - Thanks for your comments. I am learning here and what you say makes *way* more sense than what my colleague says :) – Scott Holtzman May 23 '17 at 01:32

2 Answers2

1

You can try getting rid of the subquery and use an order by with top in your main select:

SELECT TOP 1 Term
FROM uSubjectivities 
WHERE account_no = '1172014'
AND SubjectivityID = '6472140'
AND TermType = 'Common'
ORDER BY version_num desc

Here is another option for n rows:

SELECT Term
FROM (SELECT Term,
             RANK() over (ORDER BY version_num desc) AS RK
      FROM uSubjectivities 
      WHERE account_no = '1172014'
      AND SubjectivityID = '6472140'
      AND TermType = 'Common') A
WHERE RK = 1
Aaron Dietz
  • 10,137
  • 1
  • 14
  • 26
  • I thought of this too but I need to return the *n* rows per latest version number. – Scott Holtzman May 22 '17 at 15:35
  • @ScottHoltzman Overlooked that, added another way.. Side note, It seems pretty clear that ADO is behaving badly, as your queries are fine the way you have them. Maybe it will handle `RANK()` better, but that may be a hopeful thought. – Aaron Dietz May 22 '17 at 15:59
  • agreed @AaronDietz. I am trying the Rank now. – Scott Holtzman May 22 '17 at 16:02
  • unfortunately that performed worse then all options 46 seconds total time for all the calls. – Scott Holtzman May 22 '17 at 16:10
  • @ScottHoltzman Ha, bummer. Sorry. There has to be someone around who is familiar with this. The only other thing I notice is that in your original queries, `TermType = 'Common'` isn't in your subquery filters. You'll likely be better off putting it both inside and outside the subquery, as you have with your other filters. I highly doubt that will solve the issue, just something to note. – Aaron Dietz May 22 '17 at 16:16
0

You are having an issue called Correlated subquery:

In a SQL database query, a correlated subquery (also known as a synchronized subquery) is a subquery (a query nested inside another query) that uses values from the outer query. Because the subquery may be evaluated once for each row processed by the outer query, it can be inefficient.

see here another example:

 SELECT employee_number, name
   FROM employees AS emp
   WHERE salary > (
     SELECT AVG(salary)
       FROM employees
       WHERE department = emp.department);

To solve this, you have to join the result instead to do another select inside your first select, check this answer here

developer_hatch
  • 15,898
  • 3
  • 42
  • 75