October 7, 2008 at 8:45 am
GilaMonster (10/7/2008)
That query still can't use any indexes due to the leading wildcards on almost all of the filters.Do your users really enter middle portions of account numbers, phone numbers or reference numbers?
Agreed. That is why I termed the indexes as a "minimum" requirement rather than a sufficient one.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 7, 2008 at 8:59 am
Actually, I believe that indexes will help. I can't remember where I read it, but I believe that there has been improvements in the query engine in SQL Server 2005 that will allow the use of indexes in situations like this:
...
where
lastname like '%one%';
I did a query like that in a sandbox database and it did an index scan using the index on lastname I created for the test.
😎
October 7, 2008 at 1:10 pm
Lynn Pettis (10/7/2008)
Actually, I believe that indexes will help. I can't remember where I read it, but I believe that there has been improvements in the query engine in SQL Server 2005 that will allow the use of indexes in situations like this:I did a query like that in a sandbox database and it did an index scan using the index on lastname I created for the test.
Oh it will scan, but that's the best that SQL can do and scans are expensive operations. There's no way to do an index seek with a leading wild card.
It's like asking someone to search through the phone book and find every person with a surname that contains 'row'. It's doable, but requires reading the entire phone book. Much easier to find all surnames starting with 'Bro'
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 1:13 pm
tendayit (10/7/2008)
Hi,Users can enter a full accountid, idnumber, etc and retieve an account but they can also do partial searches where they put in a string say on the account number field e.g '067', they get a maximum 100 entries displayed to them of info that satisfies their criteria and then they pick the person that they want.
Which is more common? Is there a way to distinguish (at either app or DB level) whether it's an exact match or full wildcard search?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 1:17 pm
True, Gail. I remember, however, when SQL would ignore the indexes entirely if you started a LIKE clause with a wild card and just do a table scan. On very large tables, the index scan and bookmark lookup can be faster.
😎
October 7, 2008 at 1:35 pm
Lynn Pettis (10/7/2008)
On very large tables, the index scan and bookmark lookup can be faster.
You sure about that? I can see an index scan been marginally faster than a table/clustered index scan if the index contains all the needed columns.
Bookmark lookups are among the most expensive operations there are, because they are single row clustered index seeks. SQL by preference won't do bookmark lookups if it needs to look up more than 1% of the table.
I can't imagine that on (for eg) a 10000 row table that a scan of a nc index followed by 10000 clustered index seeks would be faster than a scan of the cluster.
Got an example?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 7, 2008 at 2:32 pm
Here's the fall back, "it depends". How wide are the records in the heap/clustered index, how many records in the table, how many records are being returned by the query, is the clustered index (if one) the one being used by the query. We could go on. It really comes down to test, test, and test again.
Edit: And with INCLUDED columns in SQL 2005/2008, it may be possible to reduce the need for going to clustered index at all. I would be very careful on what fields I'd include. Sorry, DTA recommended some indexes with 30 or 40 included columns on our PeopleSoft Financial database; I don't think so...
😎
October 7, 2008 at 7:17 pm
tendayit (10/7/2008)
Hi,Please find attached the modified code.
Thanks
Tendayi
Your new proc delivers bad SQL if more than one input parameter is provided... here's what the Dynamic SQL it creates looks like when such a thing happens...
SELECT DISTINCT DD.AccountId, DD.IDNumber, DD.Name, DD.Surname, DD.ClientRef1, DD.ClientRef2, LA.ClientName, DD.DebtorTypeId
FROM dbo.DebtorDetail DD WITH (NOLOCK)
LEFT OUTER JOIN dbo.LinkAccount LA WITH (NOLOCK) ON DD.LinkAccountId = LA.LinkAccount
LEFT OUTER JOIN dbo.ContactNUmber CN WITH(NOLOCK) ON CN.AccountId = DD.AccountId
WHERE DD.AccountId LIKE '%TestMe1%' OR OR OR OR OR ORDER BY DD.AccountId ASC
Yes.. I reformatted it a bit for readability on this forum... but notice all the "ORs". Fundamental flaw in the logic there. I'll work on it a bit...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 7, 2008 at 7:57 pm
TenDayIT...
As it turns out, your original code had the same flaw as the modified code... bad Dynamic SQL if more than 1 parameter is provided.
I can help, but I need more information. In order to help, I need you to generate the CREATE TABLE code for each of the tables involved in the query, please. I'll also throw in some code that will make it less likely for the code to suffer and SQL Injection attack. We'll worry about indexes later...
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2008 at 7:17 am
Hi,
Please find the attached tables.
Thanks
Tendayi
October 8, 2008 at 6:36 pm
Thanks... I'll take a look.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 9, 2008 at 7:04 am
Try this, see if it does what you need:
ALTER PROCEDURE [dbo].[MatterSearch]
@AccountId varchar(20) = NULL,
@IDNumber varchar(20) = NULL,
@ClientRef1 varchar(255) = NULL,
@ClientRef2 varchar(255) = NULL,
@Surname varchar(100) = NULL,
@TelNumber varchar(20) = NULL
AS
--searches for a matter based on the criteria passed
SET ROWCOUNT 100
SELECT DISTINCT(DD.AccountId), DD.IDNumber, DD.Name, DD.Surname,
DD.ClientRef1, DD.ClientRef2, LA.ClientName, DD.DebtorTypeId
FROM DebtorDetail DD (NOLOCK)
LEFT OUTER JOIN LinkAccount LA (NOLOCK)
ON DD.LinkAccountId = LA.LinkAccount
LEFT OUTER JOIN ContactNUmber CN (NOLOCK)
ON CN.AccountId = DD.AccountId
WHERE DD.AccountId LIKE '%' + @AccountId + '%'
UNION
SELECT DISTINCT(DD.AccountId), DD.IDNumber, DD.Name, DD.Surname,
DD.ClientRef1, DD.ClientRef2, LA.ClientName, DD.DebtorTypeId
FROM DebtorDetail DD (NOLOCK)
LEFT OUTER JOIN LinkAccount LA (NOLOCK)
ON DD.LinkAccountId = LA.LinkAccount
LEFT OUTER JOIN ContactNUmber CN (NOLOCK)
ON CN.AccountId = DD.AccountId
WHERE DD.IDNumber LIKE '%' + @IDNumber + '%'
UNION
SELECT DISTINCT(DD.AccountId), DD.IDNumber, DD.Name, DD.Surname,
DD.ClientRef1, DD.ClientRef2, LA.ClientName, DD.DebtorTypeId
FROM DebtorDetail DD (NOLOCK)
LEFT OUTER JOIN LinkAccount LA (NOLOCK)
ON DD.LinkAccountId = LA.LinkAccount
LEFT OUTER JOIN ContactNUmber CN (NOLOCK)
ON CN.AccountId = DD.AccountId
WHERE DD.ClientRef1 LIKE '%' + @ClientRef1 + '%'
UNION
SELECT DISTINCT(DD.AccountId), DD.IDNumber, DD.Name, DD.Surname,
DD.ClientRef1, DD.ClientRef2, LA.ClientName, DD.DebtorTypeId
FROM DebtorDetail DD (NOLOCK)
LEFT OUTER JOIN LinkAccount LA (NOLOCK)
ON DD.LinkAccountId = LA.LinkAccount
LEFT OUTER JOIN ContactNUmber CN (NOLOCK)
ON CN.AccountId = DD.AccountId
WHERE DD.ClientRef2 LIKE '%' + @ClientRef2 + '%'
UNION
SELECT DISTINCT(DD.AccountId), DD.IDNumber, DD.Name, DD.Surname,
DD.ClientRef1, DD.ClientRef2, LA.ClientName, DD.DebtorTypeId
FROM DebtorDetail DD (NOLOCK)
LEFT OUTER JOIN LinkAccount LA (NOLOCK)
ON DD.LinkAccountId = LA.LinkAccount
LEFT OUTER JOIN ContactNUmber CN (NOLOCK)
ON CN.AccountId = DD.AccountId
WHERE DD.Surname LIKE '%' + @Surname + '%'
UNION
SELECT DISTINCT(DD.AccountId), DD.IDNumber, DD.Name, DD.Surname,
DD.ClientRef1, DD.ClientRef2, LA.ClientName, DD.DebtorTypeId
FROM DebtorDetail DD (NOLOCK)
LEFT OUTER JOIN LinkAccount LA (NOLOCK)
ON DD.LinkAccountId = LA.LinkAccount
LEFT OUTER JOIN ContactNUmber CN (NOLOCK)
ON CN.AccountId = DD.AccountId
WHERE DD.name LIKE '%' + @Surname + '%'
UNION
SELECT DISTINCT(DD.AccountId), DD.IDNumber, DD.Name, DD.Surname,
DD.ClientRef1, DD.ClientRef2, LA.ClientName, DD.DebtorTypeId
FROM DebtorDetail DD (NOLOCK)
LEFT OUTER JOIN LinkAccount LA (NOLOCK)
ON DD.LinkAccountId = LA.LinkAccount
LEFT OUTER JOIN ContactNUmber CN (NOLOCK)
ON CN.AccountId = DD.AccountId
WHERE CN.Number LIKE '%' + @TelNumber + '%'
AND CN.ContactNumberStatusId NOT IN (4,5,6,7)
ORDER BY DD.AccountId ASC
SET ROWCOUNT 0
It still won't do index seeks, but this is usually faster than OR statements.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 10, 2008 at 9:28 am
Hi,
I have been using the code attached for 2 days now. It seemed like things were ok up until this afternoon. Users got the error message below:
The following error has occurred
Error: Item cannot be found in the Collection corresponding to the requested name or ordinal.
Please contact system administrator
They were several of them. As a result, disk activity shot up and has been high since. The call centre application is written in vb6, which uses this sql procedure. I added the SET NOCOUNT ON line as someone was saying that it missing was causing the problem. Is this the case?
Tendayi
October 10, 2008 at 10:44 am
tendayit (10/10/2008)
The following error has occurredError: Item cannot be found in the Collection corresponding to the requested name or ordinal.
Please contact system administrator
This is a client code or VB6 error, not a SQL Server error.
They were several of them. As a result, disk activity shot up and has been high since. The call centre application is written in vb6, which uses this sql procedure. I added the SET NOCOUNT ON line as someone was saying that it missing was causing the problem. Is this the case?
Possibly. Try it and see.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
October 10, 2008 at 10:47 am
tendayit (10/10/2008)
I added the SET NOCOUNT ON line as someone was saying that it missing was causing the problem.
You may need to move the corresponding SET NOCOUNT OFF to right before the EXEC(...) statement.
Though I thought that ADO solved all of those SET NOCOUNT problems that DAO and RDO had.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 15 posts - 16 through 30 (of 32 total)
You must be logged in to reply to this topic. Login to reply