problem upgrading a query from mysql to mssql
-
Yes that's what I meant as well.
Using the covering index in both queries turned out at 60%/40%.
Using the covering for the derived table method but targeted indexes for the union query was ~80%/20%
I just added another option which is to avoid the dervied table altogether and simply use the case statement in the order by clause. It turns out to perform the same as the derived table method. (the optimizer probably sees them as exactly the same thing)
eg.
Select case
when familyname like 'A%' then familyname + ', ' + givenName
when givenName like 'A%' then givenName + ' ' + familyname end as FullName from tblInstructors WITH ( INDEX( fullname ) )
where
familyname like 'A%' or givenname like 'A%'
order by case
when familyname like 'A%' then familyname + ', ' + givenName
when givenName like 'A%' then givenName + ' ' + familyname end
Not sure what I am learning with all this but I am having some fun
I guess one thing I have confirmed a few things for myself.
1) When faced with two options avoid the one with the join.
2) Consider new indexes.
3) Always run all options in QA and compare results.
I am still surprised by the 80/20 difference in that one comparison. Both are in effect using covering indexes but one is four times faster... I'll have to populate a large table to test on...any ideas?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
There's one thing you forgot. Don't trust the execution plan. Use the profiler and see the actual execution stats before making a decision. I've already see 50/50 plans that had a huge difference on the real execution.
Also don't drop the joins, they are a great help.. mayby not in this case however.
-
Good advice. I have just started to use profiler. I have much to learn there. I don't go out of my way to avoid joins, I normalize to a good degree after all.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Also, it all depends on what data you have. You can compare execution plans and/or stats all day in a test environment, but the real question is how the queries compare when executed on the real server.
Also, of course the union query needs a nonclustered index on (firstname [, ...]) and another one on (lastname [, ...]) to be able to do two index seeks. Of course if there are a lot of rows that match the clauses then index scans will be used instead.
-
And where's the original poster at?? We're about ready to close this thread out
.
Viewing 5 posts - 16 through 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply