June 1, 2004 at 10:40 pm
I have a query that runs much better with literal values for variables than with bind variable, but I don't know why.
The query is as follows.
SELECT A.*
FROM (SELECT DISTINCT (A.OBJECTID),A.NAME,A.ISLOCALIZED,A.LOCKED
FROM PTVUSERGROUPS A, PTOBJECTSECURITY B,PTVGROUPMEMBERSHIP C, PTMIGRATION M
WHERE C.USERID=203852 AND C.GROUPID=B.GROUPID AND A.OBJECTID=B.OBJECTID AND B.CLASSID=2 AND A.FOLDERID =411 AND M.OBJECTID=A.OBJECTID AND M.CLASSID=B.CLASSID) A
ORDER BY LOWER(A.NAME) ASC
This query runs well, here is the performance info:
Table 'PTOBJECTLOCKS'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0.
Table 'PTUSERLINKS'. Scan count 20, logical reads 76, physical reads 0, read-ahead reads 0.
Table 'PTGROUPSINGROUPS'. Scan count 1, logical reads 567, physical reads 0, read-ahead reads 0.
Table 'PTMIGRATION'. Scan count 20, logical reads 76, physical reads 0, read-ahead reads 0.
Table 'PTOBJECTSECURITY'. Scan count 4, logical reads 12, physical reads 0, read-ahead reads 0.
Table 'PTUSERGROUPS'. Scan count 1, logical reads 403, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 182 ms, elapsed time = 182 ms.
Only about 1000 logical reads and only 182 ms.
However, when bind variables are used instead of the variables, performance is terrible.
declare @p1 int
declare @p2 int
declare @p3 int
set @p1=203852
set @p2=2
set @p3=411
SELECT A.*
FROM (SELECT DISTINCT (A.OBJECTID),A.NAME,A.ISLOCALIZED,A.LOCKED
FROM PTVUSERGROUPS A, PTOBJECTSECURITY B,PTVGROUPMEMBERSHIP C, PTMIGRATION M
WHERE C.USERID=@p1 AND C.GROUPID=B.GROUPID AND A.OBJECTID=B.OBJECTID AND
B.CLASSID=@p2 AND A.FOLDERID =@p3 AND
M.OBJECTID=A.OBJECTID AND
M.CLASSID=B.CLASSID) A
ORDER BY LOWER(A.NAME) ASC
Table 'PTOBJECTLOCKS'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0.
Table 'PTUSERGROUPS'. Scan count 686323, logical reads 2171083, physical reads 0, read-ahead reads 0.
Table 'PTOBJECTSECURITY'. Scan count 1, logical reads 1410, physical reads 0, read-ahead reads 0.
Table 'PTGROUPSINGROUPS'. Scan count 31, logical reads 114, physical reads 0, read-ahead reads 0.
Table 'PTUSERLINKS'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0.
Table 'PTMIGRATION'. Scan count 1, logical reads 501, physical reads 0, read-ahead reads 0.
SQL Server Execution Times:
CPU time = 16032 ms, elapsed time = 28219 ms.
Obviously the optimizer is not 'peeking' into bind variable contents when executing the second query, does anyone know how to modify the behavior of the second query?
Thx, Jim
June 2, 2004 at 7:57 am
First off which did you run first. If you ran the one with the variables then the other you have to consider the fact cached data was involved (which to me looks the case).
You need to use DBCC FREEPROCACHE and DBCC DROPCLEANBUFFERS between each to get a more accurate comparison (Note: Only perform on a non-production system unless you can handle the hit your server will take on performance).
I would also consider changing to a ansi compliant join schema.
SELECT
DISTINCT
A.OBJECTID,
A.[NAME] [NAME],
A.ISLOCALIZED,
A.LOCKED
FROM
PTVUSERGROUPS A
INNER JOIN
PTOBJECTSECURITY B
INNER JOIN
PTVGROUPMEMBERSHIP C
ON
C.GROUPID = B.GROUPID
ON
A.OBJECTID = B.OBJECTID
INNER JOIN
PTMIGRATION M
ON
M.OBJECTID = A.OBJECTID AND
M.CLASSID = B.CLASSID
WHERE
C.USERID = @p1 AND
B.CLASSID = @p2 AND
A.FOLDERID = @p3
ORDER BY
LOWER([NAME]) ASC -- Note: ASC is default and understood, and LOWER only makes a difference if your server is case sensitive.
Additionally in the above the inner join of c to b occurrs before that of b to a and then ab to m based on order of parsing that occurrs.
June 6, 2004 at 6:49 pm
Bind variable generally will not be 'peeked into' in deriving the query plan, and generally you cannot do much about it. If they are parameters of a stored procedure they might be. Anyway it does look like some odd things are happening.
You have no physical or read-ahead reads for either case, which mean all the data was in buffer both times. The difference therefore is purely CPU time.
Is it possible any of those 'ID' fields are not integer ? I'm wondering if an implicit conversion is happening.
Check that statistics are up to date. To referesh the statistics on each table: update statistics 'tablename'
June 11, 2004 at 11:38 am
Thanks for your input.
The ID fields are integers, and stats are up to date.
I'm more accustomed to working in Oracle, where there are various mechanisms for handling problems like this, including hints and the ability to direct the use of a specific query plan for problem queries (granted the need for such hooks is greater in Oracle, but that's another story).
Is it indeed the case that the only viable SQL Server options are to rewrite the query manually or rewrite the application?
- Jim
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply