SQL Server Performance Forum – Threads Archive

What is the difference?!!!

What is the difference between these 3 statements: 1- SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > 32 This takes about 2 seconds. 2- SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > (SELECT schYearID FROM scholasticyear WHERE currentYear = 1) This takes about 45 seconds!! 3- DECLARE @SchYearID INT SET @SchYearID = (SELECT SchYearID FROM ScholasticYear WHERE CurrentYear = 1) SELECT TOP 1 StudentIDFROM UniqStdSchBasicData WHERE SchYearID > @SchYearID This takes more than 2 Mins!!!! lextoR

Do you have any index on SchYearID column.
Also update statistics and how many rows your table has.
Something is wrong as these query should not take so much time.

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by ranjitjain Do you have any index on SchYearID column. Also update statistics and how many rows your table has. Something is wrong as these query should not take so much time. <hr height="1" noshade id="quote"></blockquote id="quote"> Yes the SchYearID column is indexed. and the table schYeatID now has only 32 rows! lextoR

Have you checked the execution plans? The statistics are updated? How many rows are in UniqStdSchBasicData? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Can you check execution plan for this query.
and how many rows and index details for this table. SELECT SchYearID FROM ScholasticYear WHERE CurrentYear = 1

1 Querying only one table
2 Querying two tables
3 Dynamic SQL Third should be DECLARE @SchYearID INT
Select @SchYearID = SchYearID FROM ScholasticYear WHERE CurrentYear = 1
SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > @SchYearID to avoid error if the resultant is more than one value How many rows do each table have? Madhivanan Failing to plan is Planning to fail

Also Top should be used only if you use Order by Clause otherwise it will randomly return a value Madhivanan Failing to plan is Planning to fail

Madhivanan, what is dynamic about
DECLARE @SchYearID INT
SET @SchYearID = (SELECT SchYearID FROM ScholasticYear WHERE CurrentYear = 1)
SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > @SchYearID ? —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs

Essentially the main difference is that in the 2nd and 3rd examples you are selecting a value for schYearId from the scholasticyear table where the currentYear is 1. Whereas in the first example you have hardcoded a value for schYearId. The third example is slower than the second example because you are running it as two seperate statements, whereas in the second example you run it as part of the one query. With regards to overall performance, this has nothing to do with whether you have an index or not on the schYearId column in UniqStdSchBasicData. You’ve already proved that the first query runs fine. The problem lies with the search for a schYearId based on the currentYear column in the scholasticyear table. So that’s where I’d look at a candidate for an index (possibly a clustered, depending on the uniqueness of that column?). Also, try this for the first part of the third statement (not sure how much difference that would make: DECLARE @SchYearID INT SELECT @SchYearID = SchYearID FROM ScholasticYear WHERE CurrentYear = 1 As has been mentioned, you need to use the ORDER BY clause otherwise the results will be ordered based on the clustered index in the table by default. Hope that helps, Karl <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by plextoR What is the difference between these 3 statements: 1- SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > 32 This takes about 2 seconds. 2- SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > (SELECT schYearID FROM scholasticyear WHERE currentYear = 1) This takes about 45 seconds!! 3- DECLARE @SchYearID INT SET @SchYearID = (SELECT SchYearID FROM ScholasticYear WHERE CurrentYear = 1) SELECT TOP 1 StudentIDFROM UniqStdSchBasicData WHERE SchYearID > @SchYearID This takes more than 2 Mins!!!! lextoR <hr height="1" noshade id="quote"></blockquote id="quote"> Karl Grambow www.sqldbcontrol.com

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Madhivanan, what is dynamic about <pre id="code"> DECLARE @SchYearID INT SET @SchYearID = (SELECT SchYearID FROM ScholasticYear WHERE CurrentYear = 1) SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > @SchYearID </pre id="code"> ? – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Heute schon gebloggt?<a target="_blank" href=http://www.insidesql.de/blogs>http://www.insidesql.de/blogs</a> <hr height="1" noshade id="quote"></blockquote id="quote"> Well. At first look I thought Exec is used. Thanks for pointing that [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

plextoR, Could you give us the datatype of the schYearID column in both the UniqStdSchBasicData table and the scholasticyear table? There could be a datatype precedence problem here.
-Hartmut5

The scholasticYear table has only 32 tables.

quote:Could you give us the datatype of the schYearID column

Id is INT

quote:The problem lies with the search for a schYearId based on the currentYear column in the scholasticyear table

The folowing statements takes 0 seconds!!!
SELECT schYearID FROM scholasticyear WHERE currentYear = 1 DECLARE @SchYearID INT
SELECT @SchYearID = SchYearID FROM ScholasticYear WHERE CurrentYear = 1
FYI,
– The UniqStdSchBasicData is a view that returns count of 1422408 rows in 12 seconds. – The id field of the StdSchBasicData table (not the uniq.. View) has 1 culstered index and one non-clusterd.
actually i don’t know what is the idea behind that! but i also don’t think that this is the problem. in the execution plan i found that: For statement 1:
Clustered Index Scan -> Cost 86% Arguments -> OBJECT(StdSchBasicData.[PK_StdSchBasicData]),WHERE: (StdSchBasicData.SchYearID=32)
———— For statement 2:
Hash Match/Aggregate -> Cost 40% Arguments -> HASH: (StdSchBasicData.StudentID,StdSchBasicData.SchYearID), RESIDUAL(StdSchBasicData.StudentID = StdSchBasicData.StudentID AND StdSchBasicData.SchYearID=StdSchBasicData.SchYearID),[expr1004]=MAX(StdSchBasicData.StdSchBasicDat Clustered Index Scan -> Cost 58% Arguments -> StdSchBasicData.[PK_StdSchBasicData]
———— For statement 3:
The first part (setting the @SchYearID param) 0.07%
The Second Part:
Hash Match/Aggregate -> Cost 39% Arguments -> as statement no 2
Clustered Index Scan -> Cost 58% Arguments -> as statement no 2

This is statement 3: 3- DECLARE @SchYearID INT
SELECT @SchYearID = SchYearID FROM ScholasticYear WHERE CurrentYear = 1
—
SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > @SchYearID The first part takes 0 seconds as you pointed out. So it is the second part that must be taking 2 minutes. But, the second part is identical to the first statement, "SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > 32" except for the fact that you have hardcoded the value of 32 in the first statement. Now, you said that the first statement only takes 2 seconds. So what we’ve got to find out is why the last part of the third statement takes 2 minutes when you pass a parameter value to it as opposed to 2 seconds when you’re using a hardcoded value. It could be that different values (other than 32) cause it to run slower or bad execution plan. The hash/match aggregate doesn’t make sense either. Anyway, I’d start by looking at the first statement and passing different values to it and also experimenting with passing parameter values to it.

quote:Originally posted by plextoR The scholasticYear table has only 32 tables.

quote:Could you give us the datatype of the schYearID column

Id is INT

quote:The problem lies with the search for a schYearId based on the currentYear column in the scholasticyear table

Karl Grambow www.sqldbcontrol.com

Hi ya, The problem is that in statements 2 and 3 SQL doesn’t know what the value of SchYearID is going to be at the time that the statements are compiled as a single batch. To get around that you could split the batch into two, either by first getting the value of SchYearID and passing it back to the application, or if this is all in a proc then saving it as a local variable and then call the final statement either from the application or by calling a sub-procedure and passing SchYearID as a parameter also the index on StdSchBasicData.SchYearID appears to be clustered index (or part of it), which is not going to be that good for performance. A non-clustered index on that column would help Cheers
Twan

<blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by plextoR What is the difference between these 3 statements: 1- SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > 32 This takes about 2 seconds. 2- SELECT TOP 1 StudentID FROM UniqStdSchBasicData WHERE SchYearID > (SELECT schYearID FROM scholasticyear WHERE currentYear = 1) This takes about 45 seconds!! 3- DECLARE @SchYearID INT SET @SchYearID = (SELECT SchYearID FROM ScholasticYear WHERE CurrentYear = 1) SELECT TOP 1 StudentIDFROM UniqStdSchBasicData WHERE SchYearID > @SchYearID This takes more than 2 Mins!!!! lextoR <hr height="1" noshade id="quote"></blockquote id="quote">Twan is right about why queries behaves the way you described and possible workarounds. Another solution you can also try is: <pre id="code">select top 1 StudentID from scholasticyear y inner loop join UniqStdSchBasicData d (index=<index on schYearID&gt<img src=’/community/emoticons/emotion-5.gif’ alt=’

‘ /> on d.SchYearID > y.SchYearID where y.currentYear = 1 option (force order)</pre id="code">

In all 3 cases, the database is doing a clustered index scan on the StdSchBasicData table. Usually this means that it is reading the entire table, but in this case, with "top 1", it is most likely scanning records just until it finds one that matches the criteria. I believe it was stated that the table has about 1.4 million rows. This will take a long time to read, depending on how soon it finds a match. If no rows meet the criteria, then it will end up reading all 1.4 million rows before returning a result. If it finds a match in the first couple rows, it will return a result in less than a second. The execution time will also depend a lot on whether or not the data pages are already in the buffer pool or not. To truly test the performance, you will probably want to run DBCC DROPCLEANBUFFERS before each query to ensure that no data pages are stored in cache before the query is run. It would be helpful to see the entire execution plan, as it looks like it is doing a hash-type self join on the 1.4 million row table for the second and third queries. You can get the full execution plan by using SET SHOWPLAN_TEXT ON before running the query (this will not actually execute the query, it will just give you the plan). In any case, you generally want to avoid clustered index scans, especially on large or wide tables. Non-clustered index scans are better than clustered, but index seeks are what you really want when dealing with a large amount of data. Double check to make sure that there is an index on the SchYearID column in the StdSchBasicData table.
-Hartmut5

]]>