performance issue with a query
-
SELECT AL2.vch_shortname, AL3.i_event_id, AL1.i_users_id, AL1.vch_lastname,AL1.vch_firstname
FROM dbo.users AL1, dbo.course AL2,
dbo.event AL3, dbo.event_user AL4
WHERE (AL2.vch_course_id=AL3.vch_course_id AND AL3.i_event_id=AL4.i_event_id
AND AL4.i_users_id=AL1.i_users_id) AND (AL2.vch_shortname='S261')
This SQL is generated by a reporting tool GUI. when I run this query in query analyser the cpu goes out of control. however when I run the same query without the last column in the select list it runs like a horse. I checked the query plans for both the queries they are completely different. the second query uses the parallelism and the first one uses merge join on the users and event_user tables.
I am running these queries on a dual processor 1GB RAM.
Could some one please help
-
SELECT AL2.vch_shortname, AL3.i_event_id, AL1.i_users_id, AL1.vch_lastname, AL1.vch_firstname
FROM dbo.users AL1
JOIN dbo.event_user AL4 ON AL4.i_users_id = AL1.i_users_id
JOIN dbo.event AL3 ON AL3.i_event_id = AL4.i_event_id
JOIN dbo.course AL2 ON AL2.vch_course_id = AL3.vch_course_id
WHERE AL2.vch_shortname = 'S261'
Little Easier for me to read this way....What indexes do you have on AL1 and AL2 ?
Once you understand the BITs, all the pieces come together
-
Thanks for ypur help
I have clustered index on vch_course_id on AL2 and indexes on other fields not being used in the query. on the Al1 I have a clustered index on i_users_id and an index on lastname.
however I played around with the indexes on this table by creating a covering index on first name and lastname but it did not help.
-
OK, rewriting your query, just so we can break apart some items...
is there the same performance diff. using the following query with/without firstname?
SELECT AL2and3.vch_shortname, AL2and3.i_event_id, AL1.i_users_id, AL1.vch_lastname, AL1.vch_firstname
FROM dbo.users AL1
JOIN dbo.event_user AL4 ON AL4.i_users_id = AL1.i_users_id
JOIN ( SELECT AL2.vch_shortname, AL3.i_event_id
FROM dbo.event AL3
JOIN dbo.course AL2 ON AL2.vch_course_id = AL3.vch_course_id
WHERE AL2.vch_shortname = 'S261' ) AL2and3
ON AL2and3.i_event_id = AL4.i_event_id
I hope I got everything so it results the same.
About how many records result in the AL2and3 derived table query (AL3 JOINed with AL2 only)?
Once you understand the BITs, all the pieces come together -
I really appreciate your help on this
the derived table AL2and3 has 45 rows the query takes exactly the same time as before and the execution plan is also the same. Also what I am not able to understand is why does this query behave so weird only by adding another column in the select list.
-
OK, I'm just step by step "simplifing" (I hope) so we can drill down a little...
Now Try this, (with and without)...
If Object_ID('TempDB..#Temp') is Not NULL Drop Table #Temp
SELECT AL2.vch_shortname, AL3.i_event_id
INTO #Temp
FROM dbo.event AL3
JOIN dbo.course AL2 ON AL2.vch_course_id = AL3.vch_course_id
WHERE AL2.vch_shortname = 'S261'
SELECT AL2and3.vch_shortname, AL2and3.i_event_id, AL1.i_users_id, AL1.vch_lastname, AL1.vch_firstname
FROM dbo.users AL1
JOIN dbo.event_user AL4 ON AL4.i_users_id = AL1.i_users_id
JOIN #Temp AL2and3
ON AL2and3.i_event_id = AL4.i_event_id
-- Cleanup
If Object_ID('TempDB..#Temp') is Not NULL Drop Table #Temp
Same result? "with much slower than without"
Once you understand the BITs, all the pieces come together -
This works like magic But I am confused I thought temp tables are suppose to be slower than inner joins
-
No "rule" is always...
With just using the 1 "compound" SELECT , we where giving the optimizer access to ALL aspects of all the tables (Columns, Indexes, Rowcounts etc..) but when we introduced the #Temp table, the optimizer may only be using the 2 columns of the #Temp table.
Do you "have to have" a single compound SELECT?
There may be ways to "force" the optimizer to NOT use the what it thinks is the best plan when firstname is included.
Once you understand the BITs, all the pieces come together -
Thanks for your help I used a view instead of temp table because the reporting tool we are using cannot use temp tables.
-
Glad we could help.
So you're all set for now?
P.S. Heading home for the day
Once you understand the BITs, all the pieces come together
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply