problem upgrading a query from mysql to mssql
-
Ive just upgraded my site from using mysql to mssql, and im having trouble with a query.
This is the query which i have in mysql, what it does is select all the records which the firstname or the surname match a certain character as the first character.
e.g.
if the first letter was A it would return
andrea smith
adam johnston
azaz, kristy
what the if statement does is works out if the firstname or surname starts with A. The if the firstname starts with a it return firstname surname but if the surname starts with A it will return surname, firstname
MYSQL CODE
SELECT IF(surname LIKE '" & arrCharacters(0) & "%',TRIM(CONCAT(surname, ', ', firstname)),TRIM(CONCAT(firstname, ' ', surname))) AS fullname FROM tblPeople WHERE surname LIKE '"& arrCharacters(0) &"%' OR firstname LIKE '"& arrCharacters(0) &"%' ORDER BY fullname
Thanks in advance.
-
Select case when Surname like 'A%' then Surname + ', ' + FirstName
when FirstName like 'A%' then FirstName + ', ' + Surname
end
from....
BTW this is something that would be faster to handle at the client. The server is meant to sent the data, the client to present it and this is really a presentation issue.
-
Agree with Remi, this sounds weird for the database to handle. But if needed I would probably code it like this:
SELECT fullname FROM (
SELECT firstname + ',' + surname as fullname
FROM tblpeople
WHERE firstname LIKE 'A%'
UNION
SELECT surname + ',' + firstname
FROM tblpeople
WHERE surname LIKE 'A%'
) foo
ORDER BY fullname
-
Chris, Is the union faster then using a CASE in the derived table?
select foo.FullName from
(Select case when lname like 'A%' then lname + ', ' + fName when fName like 'A%' then fName + ', ' + lname end as FullName from tblPeople) foo
order by fullname
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
No it's faster (assuming it is) because both search condition can use a single index seek, instead of possibly a scan. So I'd definitly check out the union version.
-
Cool I'll have to test that. I would have assumed otherwise.
I would have assumed the derived table would use an index seek to get the records and then all the outter statement has to do is order them. The derived table is not indexed so I guess it wouldn't use the index for the ordering.
I'll have to run them both through the QA later and check out the execution plan. I use allot of derived tables in one project and maybe I should be doing a union.
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Note the WHERE clauses. If we where to select all the rows and just decide if firstname or surname should come first in fullname, then the union would be much slower. But since we are only after rows where either firstname or surname starts with the character A we need to apply the WHERE clause. For the case statement we would need a clause like "firstname = 'A%' OR surname = 'A%'", and depending on indexing we might get in trouble with that. Anyway I think the union is clearer on what the query does.
-
I run a quick test in QA and it looks like a single derived table is faster.
In my test db neither family nor givennames are indexed. It also has a very small number of total rows.
I ran this
single derived table)--------------
select foo.FullName from
(Select case when familyname like 'A%' then familyname + ', ' + givenName when givenName like 'A%' then givenName + ' ' + familyname end as FullName from tblInstructors
where
familyname like 'A%' or givenname like 'A%'
) foo
order by fullname
--------------
And then this: (union join)
--------------
SELECT fullname FROM (SELECT givenname + ',' + familyname as fullnameFROM tblinstructors
WHERE givenname LIKE 'A%' UNION
SELECT familyname + ',' + givenname
FROM tblInstructors
WHERE familyname LIKE 'A%'
) foo
ORDER BY fullname
The union method does two index scans for a total of 90% of the execution time (45% each).
The single derived table does one index scan for 83%.
In all the union method shows six steps in the plane to the others three.
I suppose I could populate a table big enough to measure the time difference but it looks like the Union is less performant. Which is what my assumptions was.
Am I missing something? Are the two index scans in the union "happening" at the same time?
BTW: I am only pulling at these threads because I need to challenge my assumptions and to keep learning
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Naturally, without indexes scans will be necessary, and then the unions will perform worse since two scans are required.
-
I ran both queries again first with one index (lname) then with both lname,fname. When I run them both in the same QA window it shows a percent cost relative to the batch. The union version comes out to 60% the single derived table 40%. When I used the covering index the results were closer at 52%/48%.
I used With(index()) to force the use of each index in turn.
Interestingly enough the derived table method always did an index scan and never a seek. Whereas the union would use a seek when it could. I supppose this is your point. Perhaps with a much large table to results would skew the other way, maybe allot. (test table only has a few thousands rows).
In any case I sure am having fun with QA these days
Thanks.Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Hmm something weird. I just ran it again but in I fixed my union statement so that each select used the correct index. eg. where fname used the fname index and where lname used the lnmae index.
In the execution plan it showed queries in the union statements using index seeks but the cost relative to batch jumped up to 83% ?? I would have expected the cost to go down?
Maybe I am reading the numbers backwards?
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
"TheKing",
Several solutions have been posted to your problem... did any of them do what you wanted?
--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) -
Have you tried with a covering index (1 index with both columns) and checking the derived table version?
-
Yes I did. The result using that index was 60/40 in favor of the derived table method.
When I wrote the UNION so that each select uses the appropriate index for each where claus (fname or lname) the cost went to 80% when compared to the derived table using the covering index.
Here is a screenie of the QA.
http://www.lonecrow.net/qa.gif
Dave
Trainmark.com IT Training B2B Marketplace
(Jobs for IT Instructors) -
Hmm, I meant the single query with a covering index (where both columns are in ONE index).
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply