Need to refine poor-performing query

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 2005 Forums

Transact-SQL (2005)

Need to refine poor-performing query

Author

Topic

MightyGorilla
Starting Member

7 Posts

Posted - 2007-07-16 : 08:34:17

By refine, I mean completely rewrite. :)
I have a ship-to-location table that can have records added manually through
the application side, or automatically via EDI address updates from
customers. The very-lame application only allows a 4-digit string to be used
as a key for each record.

In summary, this means I must generate (in some automated way) a new unique
key for each new address, but I must work around what is already there.
Example: The keys 0000, 0001, 0002, 0089, 00QR may already exist in the
table. I cannot alter the database to make the key field into a unique
auto-incrementing field - as that will upset the front end (which is not
open-source).

My existing solution (which is now painfully slow - after the number of
addresses it had to parse increased) involves a "constant" table consisting
of one column filled with the sequence of values 0-9999. It then selects the
MIN() value that does NOT EXIST in the actual address table.

Here is the snipet where my problem occurs:


SELECT @ship_to_new = MIN(Constant)
FROM dbo.GI_ShipToAddressConstants
WHERE Constant NOT IN (SELECT ShipToCode FROM SOB_CustShipToAddress
WHERE Division = '00'
AND CustomerNumber = @customer_number
AND ISNUMERIC(ShipToCode) = 1
)

As I listed above - the id is a string, not purely numeric, but at the time
the numeric constant table yeilded plenty of headroom, and would work around
alpha values entered manually easily enough.

This reeks of poor design. I know that. Any clean methods of accomplishing
this, or new approaches?

Thank for your time,
Travis-

nr
SQLTeam MVY

12543 Posts

Posted - 2007-07-16 : 08:51:57

ISNUMERIC(ShipToCode)
12e3
would be let through by that as would a few other things.

Bit surprised that is slow.
Do you have an index on CustomerNumber, Division, ShipToCode in SOB_CustShipToAddress

try
SELECT @ship_to_new = MIN(Constant)
FROM dbo.GI_ShipToAddressConstants c
left join (SELECT distinct ShipToCode FROM SOB_CustShipToAddress
WHERE Division = '00'
AND CustomerNumber = @customer_number
and patindex('%[^0-9]%',ShipToCode) = 0
) a
on a.ShipToCode = c.Constant
where a.ShipToCode is null

or

SELECT @ship_to_new = MIN(Constant)
FROM dbo.GI_ShipToAddressConstants c
left join SOB_CustShipToAddress a
on a.Division = '00'
AND a.CustomerNumber = @customer_number
and a.ShipToCode = right('0000' + convert(varchar(4),c.Constant), 4)
where a.ShipToCode is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

MightyGorilla
Starting Member

7 Posts

Posted - 2007-07-16 : 09:50:41

nr,
My original query typically takes 21 seconds (on our server anyway) to find the next available key. Your first attempt cut that to 13 seconds, but your second query is practically instantaneous! Excellent!

I've been trying to analyze the execution plans on my queries lately - DBA is not my only hat here - but I still don't completely grasp what it's telling me. I'll have to keep studying them. :)

Thanks a bunch,
Travis-

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-16 : 11:26:08

It is the IN part that kills your query.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources