weird! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

weird!

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2002-07-11 : 17:34:18

What is wrong with this query? When I set @text to any other letter than 'Z' it executes within 6 seconds on my server. But if I set it to 'Z' it takes forever to run. Over 1 minute and I still don't have my recordset. Pls help.

declare @txtuserid varchar(16), @txtAcctNum varchar(16), @txtCompNum varchar(20), @text varchar(10)
set @txtuserid = 'admin1'
set @txtAcctNum = '0000000000006551'
set @txtCompNum = '1012'
set @text = 'm'
-- grab cardholders in the group
SELECT DISTINCT A.txtName, UG.txtAccountNum, A.txtCompanyNum, A.lngProfileID
FROM ApprovalGroup As AG INNER JOIN UserGroups AS UG ON AG.txtInternetNum = UG.txtInternetNum AND AG.intViewGroup = UG.lngGroupNum
INNER JOIN Account AS A ON UG.txtAccountNum = A.txtAccountNum AND UG.txtInternetNum = A.txtInternetCompNum
WHERE (AG.txtUserID = @txtUserId) AND (AG.txtInternetNum = @txtCompNum) AND (A.blnValidAccount = 1) AND (A.txtName Like @text + '%')
ORDER BY A.txtName

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-11 : 17:41:49

Lets see your CREATE TABLE statemtents and your CREATE INDEX statements (Your DDL).

Michael

<Yoda>Use the Search page you must. Find the answer you will.

PeterG
Posting Yak Master

156 Posts

Posted - 2002-07-11 : 17:43:57

I can't show you my DDL as I didn't create the tables and indexes. So you think this has something to do with the way the tables and indexes were created?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-11 : 17:48:33

You should be able to use Enterprise Manager to generate the scripts for you. Right click the table in question and "Generate SQL Script."
Be sure to select the option to create indexes.

It sounds like you have a lot of "z" records, or that you have a clustered index on txtName and the z records are at the "bottom" of that index.

How man records are in that table?
How many are "z" records?

Michael

<Yoda>Use the Search page you must. Find the answer you will.

PeterG
Posting Yak Master

156 Posts

Posted - 2002-07-11 : 17:49:56

Coding directly (as doing a like 'z%' in the query) gave me the results and fast too. But I need this @text to be a variable. What can I do? How do I modify this query?

MakeYourDaddyProud

184 Posts

Posted - 2002-07-11 : 18:01:03

Is the collation sequence on your SQL Server set to differentiate between cases? However, try combining the '%' in @text rather than a textual concat in the actual qry

Daniel Small MIAP
www.danielsmall.com IT Factoring

PeterG
Posting Yak Master

156 Posts

Posted - 2002-07-11 : 18:07:30

Sorry Danny, I'm a SQL server newbie. The db is not case sensitive if that's what you're asking. How do I "try combining the '%' in @text rather than a textual concat in the actual qry ".

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-11 : 18:14:27

DECLARE @Text VARCHAR(10)
SET @text = 'm'+ '%'

SELECT @text

Like that methinks

Michael

<Yoda>Use the Search page you must. Find the answer you will.

MakeYourDaddyProud

184 Posts

Posted - 2002-07-11 : 18:18:18

... as Micheal specified the last line of your qry would now read...

quote:

WHERE (AG.txtUserID = @txtUserId) AND (AG.txtInternetNum = @txtCompNum) AND (A.blnValidAccount = 1) AND (A.txtName Like @text)

Daniel Small MIAP
www.danielsmall.com IT Factoring

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-11 : 18:22:42

Ooops! Miss typing that part!

Here's your original query fully edited:

declare @txtuserid varchar(16), @txtAcctNum varchar(16), @txtCompNum varchar(20), @text varchar(10)
set @txtuserid = 'admin1'
set @txtAcctNum = '0000000000006551'
set @txtCompNum = '1012'
set @text = 'm' + '%'

-- grab cardholders in the group
SELECT DISTINCT A.txtName, UG.txtAccountNum, A.txtCompanyNum, A.lngProfileID
FROM ApprovalGroup As AG
INNER JOIN UserGroups AS UG ON AG.txtInternetNum = UG.txtInternetNum AND AG.intViewGroup = UG.lngGroupNum

INNER JOIN Account AS A ON UG.txtAccountNum = A.txtAccountNum AND UG.txtInternetNum = A.txtInternetCompNum

WHERE (AG.txtUserID = @txtUserId)
AND (AG.txtInternetNum = @txtCompNum)
AND (A.blnValidAccount = 1)
AND (A.txtName Like @text)
ORDER BY A.txtName

Michael

<Yoda>Use the Search page you must. Find the answer you will.

MakeYourDaddyProud

184 Posts

Posted - 2002-07-11 : 18:25:06

Thats a wrap! Not sure if the parenthesis is required in the WHERE tho????

Daniel Small MIAP
www.danielsmall.com IT Factoring

PeterG
Posting Yak Master

156 Posts

Posted - 2002-07-11 : 18:31:17

I tried and still had problem with 'Z'. All others were fine.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-11 : 18:31:42

They are not required, but I didn't want to confuse him any more than I had to :)

Michael

<Yoda>Use the Search page you must. Find the answer you will.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-11 : 18:34:25

Peter, if there is a problem with 'z' then you need to give us more info, like the questions I asked earlier.

How man records are in that table?
How many are "z" records?
How many records are of another letter (like a)?
What Indexes do you have on that table (tell us if they are clustered or not).

Michael

Only thing I can think of is that you have lots of z records, which doesn't sound right.

<Yoda>Use the Search page you must. Find the answer you will.

MakeYourDaddyProud

184 Posts

Posted - 2002-07-11 : 18:39:59

Is the column indexed? If not, try enabling statistics on that column. Do a CREATE STATISTICS and re-run for 'Z%'...

CREATE STATISTICS TxtName
ON yourdb..Account (TxtName)
WITH FULLSCAN, NORECOMPUTE
GO

... tell us how it goes

Daniel Small MIAP
www.danielsmall.com IT Factoring

PeterG
Posting Yak Master

156 Posts

Posted - 2002-07-12 : 13:12:45

I looked in the execution plan and it's the same for H, M, Z, or any other letter. I asked my admin to setup indexes on the fields that I need, but it didn't help.

The fix I did was add an if statement especially for Z and had the like 'z%' hard coded. It isn't very elegant but it works.

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-07-12 : 14:50:10

Hi Peter,
just wandering why you don't answer questions. People who post here have the best intension of helping you, but they also learn along the way. There definitely seems to be a problem with your indexes, and the only answer we got from you was that you asked you DBA to set the indexes up for you. If we don't know what he did, how are we sure he was correct? At the end, who's going to benefit from your solution? Not only as you mentioned it's not elegant, it's plain wrong and not a way to go. If you want a team work here, you should answer questions. If you can't publish your exact table structure, you should change column names, drop the columns which are irrelevant, etc., but your approach kind of doesn't seem be to fair.

helena

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-07-12 : 15:04:21

Thank you helena!

Peter, you keep asking a question, and we keep trying to get you to provide DETAILED info so that we can help you. Without the info, we are just making guesses!

Help us help you!

Michael

<Yoda>Use the Search page you must. Find the answer you will.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources