Problem with TOP in query
-
Thanks for the advice
The web application builds up the SQL in a string as it has to look in another recordset to see which columns to return and supply in the featured job SQL SELECT.
On the live box the JOB table currently has 254350 rows
-PK clustered JobPK (1)
-non clustered on LiveDate
-non clustered on ClientFk (2)
-non clustered on SiteFk
-non clustered on CreateDate
-non clustered on ExpiryDate
the CLIENT table 3500
-PK clustered index clientPK (3)
-non clustered Email
The development box (& website) always has less than the max amount of featured jobs to show and always loads straight away.
When I look at the execution plan for the SQL its performing
a clustered index scan on the PK_tbl_CLIENTS_ClientPK (3)
an index seek on non clustered index nclid_tbl_JOBS_ClientFk (2)
a clustered index seek on PK_tbl_JOBS_JobPk (1)
I have saved the execution plan to an xml file on my local PC and its quite big and doesn't make much sense to me. I don't know what I should be looking for. I could post it here if it would be useful?
I am coming round to the thought that it may be something else that occurs at the same time which is blocking the jobs being returned as I have created a new test page on the site causing problems that runs the exact same SQL but always requests more featured jobs back than exist in the system and it is running fine. However what is strange is that whenever this problem seems to occur (the no of featured jobs falls
belows the requested max show amount ie 7<8) then
a) As a quick fix the admin guy goes into the site and just randomly makes another job featured and this seems to resolve the issue.
b) When I run the erroring sql in query analyser it seems to return straight away without an issue.
Also on another point I still use SET ROWCOUNT @var in SQL 2005 and it works fine. I usually use it when doing large updates/deletes to split the transaction up into lots of small batches.
-
With your top-clause, sqlserver ends the search for rows when the top-number is reached.
So if the top-number hasn't been reached, the quest goes on !
Because your plan shows a nice
clustered index scan on your client table, its indexes for the predicates are not being used !WHERE
c.SiteFK = @SITEID
Investigate why your non clustered on SiteFk isn't being used !
How many sites do you have ?
How well are they spread over a 100% scale.
Maybe you can force the index usage with specifying the new OPTIMIZE FOR query hint ! (check BOL)
I presume the predicates ... AND j.Live=1 AND j.featured = 1 ... are booleans (0/1) so creating an index on that by default will be useless.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for your advice
How can I find out why the query optimiser chooses one index over another? What steps can I go through
to find out why the nclidx_tbl_JOBS_SiteFK is not been utilised? As a client can only belong to one
site maybe it doesn't need that index as its getting the relevant information from the client table instead.
I have slightly de-normalised the tables to help reduce the no of joins back to the SITES table and there is usually always a SiteFK column in the most commonly used tables (both tbl_JOBS and tbl_CLIENTS have a siteFK column)
Also you are right in that j.Featured and j.Live are both booleans.
There are 44 sites in the system and here is the percentage of jobs that each site has. I presume that is what you meant by the spread over a 100% scale.
SiteName % of total jobs
Site1 37.20
Site2 6.59
Site3 6.40
Site4 6.03
Site5 5.64
Site6 5.43
Site7 4.16
Site8 3.56
Site9 3.21
Site10 3.18
Site11 2.75
Site12 2.66
Site13 2.00
Site14 1.78
Site15 1.23
Site16 1.20
Site17 1.08
Site18 1.03
Site19 0.97
Site20 0.57
Site21 0.55
Site22 0.51
Site23 0.49
Site24 0.42
Site25 0.41
Site26 0.25 (problem site)
Site27 0.19
Site28 0.13
Site29 0.11
Site30 0.08
Site31 0.07
Site32 0.05
Site33 0.03
Site34 0.03
Site35 0.01
Site36 0.01
Site37 0.01
Site38 0.01
Site39 0.00
Site40 0.00
Site41 0.00
Site42 0.00
Site43 0.00
Also I notice that the statistics on the nclidx_tbl_JOBS_siteFk have not been updated since 23/08/2007.
Should I force an update of stats on all the tables involved?
Thanks for all your help and useful comments regarding this situation.
-
- Check that the @SITEID is defined exactly as is your column SiteFK in your table Clients. This way you avoid implicit conversions, which may cause your index not being used !
you can use SET SHOWPLAN_XML ON in your query panel!
Use it, It's a huge enhancement in case of performanceananlysis !
- If the above is OK, and the query still does not use the sitefk-index, you may want to use the proposed option like:
SELECT TOP 8
JobPK,
JobTitle,
Location,
JobDescription,
dbo.udf_PAY_FORMAT('£', SalaryMin, SalaryMax, SalaryFrame, SalaryDesc) as Salary,
j.HideContactDetails,
c.CompanyName
FROM JOBS AS j WITH (NOLOCK)
INNER JOIN CLIENTS AS c WITH (NOLOCK) ON c.ClientPK = j.ClientFK
WHERE c.SiteFK = @SITEID
AND j.Live = 1
AND j.Featured = 1
ORDER BY LiveDate DESC
OPTION ( OPTIMIZE FOR (@SITEID = site id for Site26 ) );
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Actually when I was using it, it was part of a delete statement, but here is where I got that:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.SQL.v2005.en/udb9/html/50705caa-4d0a-490f-92a4-75ece96e0a54.htm
Although the effect of SET ROWCOUNT on SELECT statements remains unchanged, using TOP with SELECT is preferred over using SET ROWCOUNT for the following reasons:
- SET ROWCOUNT causes most SELECT, INSERT, UPDATE, and DELETE statements to stop processing when they have affected the specified number of rows. This behavior applies inside the firing of triggers too.
- As a part a SELECT statement, the query optimizer can use the value of expression in the TOP clause as part of generating an execution plan for a query. Because SET ROWCOUNT is used outside a statement that executes a query, its value cannot be used to generate a query plan for a query.
-
Wow... lot's and lot's of suggestions... but I've gotta ask... the code runs almost instantaneously in QA even when there's not enough qualifying rows to satisfy TOP but causes the GUI to hang under similar circumstances. Why is there a difference?
Could it be that the wrong provider, say, ODBC instead of OLEDB, is being used in the GUI?
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
- Good point Jeff
- Also check what kind of transaction isolation level is used for the connection and / or command ! And if it is part of a cursor, what CacheSize is specified.
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
The website is classic ASP connecting to SQL Server 2005 using an ADO connection string such as
DRIVER={SQL Server}; SERVER=MYSERVER; DATABASE=MYDB; uid=MYUSER; pwd=MYPASSWORD;
I have a data helper class that runs all the SQL needed by the site which USUALLY runs the SQL as an
execute on a command object with the following settings
objCommand.CommandType = adCmdText
objCommand.CommandText = strSQL
objRecordset.CursorLocation = adUseServer
objRecordset.CursorType = adOpenStatic
objRecordset.LockType = adLockReadOnly
set objRecordset = objCommand.Execute
However in the featured job include file a specific class style is applied to the last row displayed and as I didn't
want to go down the route of doing a count first I use the .RecordCount method of the recordset object and to do
so the recordset needs to use a client side cursor and is set up like so:
objRecordset.CursorLocation = adUseClient
objRecordset.Open strSQL, objConnection,,adLockReadOnly, adCmdText
intRecordCount = objRecordset.RecordCount
Which allows me to get the count back.
At the moment the site has 9 live featured jobs and is running fine (max=8). However I have created a test page (hidden from the site users)
that has exactly the same code as the homepage but I am requesting a TOP 12 and that page is returning fine as well.
The problem does seem to be very temperamental which seems to suggest something else is going on apart from just the fact that the
SELECT can't find X jobs to display. However as I said previously it seems strange that whenever the problem has occurred we have fixed
it immediately by making X jobs featured so that the TOP can be fulfilled. Why would that fix it if it wasn't do with the TOP?
And if it was the TOP surely my test page should be hanging now as there isn't 12 jobs to show?
Maybe when some other action on the DB happens in conjunction with the insufficient TOP clause issue that the problem occurs.
Thanks for the help
-
I'm thinking that your problem is a combination of using the wrong Provider, using a client side cursor, and doing a count in the app instead of on the server.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply