Bad sql query and slow response

Can someone please confirm with me that this is not a good sql query. It
takes so long to run. ALso, can someone please re-write it for me. Thank
you.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
If you can guarantee that DLASTNAME and DFIRSTNAME are never stored with
leading ' '.
Then
SELECT * FROM CASE WHERE
DLASTNAME like 'rumfield%'
and DFIRSTNAME like 'nunu%'
order by C_codes
Would be faster, and could use an index on DLASTNAME or DFIRSTNAME.
David
|||Make sure you pre-process the column data and remove any spaces before you
hit the query. Putting functions on columns like that forces SQL Server to
table scan, making index usage impossible. And make sure you have a
composite index on lastname + firstname.
And never, never, never, ever use SELECT * in a production query.
David Gugick
Imceda Software
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
|||Not to mention that [CASE] is a HORRID table name given that it is a SQL
Server RESERVED WORD.
Also consider an index for C_codes. You should consider an Index for the
names even if you don't trim them. Why would they have leading spaces in the
names?
Sincerely,
Anthony Thomas
"James Juno" wrote:

> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
|||James,
Defining 'good' is somewhat difficult without any context but the following
may help:
Any use of a function against a column used in the where clause is likely to
result in a table scan. In your case the LTRIM(RTRIM bit is prety well
certain to result an scan as it is impossible to use an index to support it.
(You 'may' get away with RTRIM on it's own using an index, but would need to
check. But then how big is the table? If only small who cares if scan is
needed, however if it contains a lreg number of rows you will care a great
deal.
What datatype is the column - if varchar then Rtrim is probably not needed
anyway.
Avoid the Ltrim in the where clause and Ltrim stuff on input if needs be.
Look at the indexes on Dlastname and Dfirstname
Finaly if these are real names does "case" mean something sensible, and
similarly what does the D signify - but we could be opening a whole new
discussion!
Mike John
"James Juno" <JamesJuno@.discussions.microsoft.com> wrote in message
news:8D4AC99A-1578-4BE3-8F87-F44A09D4F9D4@.microsoft.com...
> Can someone please confirm with me that this is not a good sql query. It
> takes so long to run. ALso, can someone please re-write it for me. Thank
> you.
> SELECT * FROM CASE WHERE
> LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
> AND LTRIM(RTRIM(DFIRSTNAME)) like 'nunu%' order by C_codes
sql