Need Help: Slow SQL Server trigger

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)

Need Help: Slow SQL Server trigger

Author

Topic

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2003-11-21 : 05:51:58

Hi guys! I am having a serious problem with a SQL Server 2000 trigger that I created. I work in a telecommunications company, our database is online and I wanted to create a trigger that would update the users' credit amount left from 0.28 and less to 0.00 (That's because we charge per minute and 0.28 or less is not enough for a call). My trigger is this:

create trigger update_tac_val
on dw_users
after update
as
if update(tac_val)
begin
update dw_users
set tac_val = '0.00'
from dw_users
join inserted
on inserted.tac_key = dw_users.tac_key
where dw_users.tac_val like '0.28' or dw_users.tac_val like '0.27'or dw_users.tac_val like '0.26' or dw_users.tac_val like '0.25' or dw_users.tac_val like '0.24'
or dw_users.tac_val like '0.23' or dw_users.tac_val like '0.22' or dw_users.tac_val like '0.21' or dw_users.tac_val like '0.20' or dw_users.tac_val like '0.19'
or dw_users.tac_val like '0.18' or dw_users.tac_val like '0.17' or dw_users.tac_val like '0.16' or dw_users.tac_val like '0.15' or dw_users.tac_val like '0.14'
or dw_users.tac_val like '0.13' or dw_users.tac_val like '0.12' or dw_users.tac_val like '0.11' or dw_users.tac_val like '0.10' or dw_users.tac_val like '0.09'
or dw_users.tac_val like '0.08' or dw_users.tac_val like '0.07' or dw_users.tac_val like '0.06' or dw_users.tac_val like '0.05' or dw_users.tac_val like '0.04'
or dw_users.tac_val like '0.03' or dw_users.tac_val like '0.02' or dw_users.tac_val like '0.01'
end

tac_val is a text field that's why I have to go through each one separetely.
The problem is that, since our database is online (but only 30 users maximum can connect at any time), the trigger turns out to be very slow, causing a perfomance drag in SQL Server. What could be the solution in this issue? Maybe a stored procedure that would run at scheduled times?
I use SQL Server 2000 Service Pack 2 on a Windows 2000 Server machine.
If you have any suggestions, please let me know. I would appreciate it very much.
Thanks in advance!

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-21 : 06:27:01

Hi Patrick

I guess the extended WHERE clause is not helping. Why not try combining CAST and SUBSTRING on the text column, along the lines of

WHERE CAST( SUBSTRING( tac_val, 1, 4 ) AS DECIMAL(4,2) ) <= 0.28

This is restricted to only the first four characters of the column, so if the column stores figures greater than 10 (e.g. '23.28') then you could use the CHARINDEX to find the decimal point and add two to the value to get the substring length you need.

Raymond

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2003-11-21 : 11:13:34

Unfortunately, this conversion didn't work because SQL Server says that it cannot convert text data type fields.

raymondpeacock
Constraint Violating Yak Guru

367 Posts

Posted - 2003-11-21 : 11:23:39

It is a valid CAST on a text column, although it will fail if you have rows where the first four characters can't be converted to a decimal.

Why do you have this detail in a text column? Can't you have a decimal column that you insert into instead?

Raymond

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2003-11-21 : 11:45:53

Can you confirm you're using a TEXT column (and not a char or varchar) to store this information. TEXT columns are typically used for fields that will hold more than 8,000 characters or data. Also using a LIKE comparison is less effecient than using an =. I'm not sure of the performance implications of using a LIKE without a wildcard character as you did.

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

X002548
Not Just a Number

15586 Posts

Posted - 2003-11-21 : 11:52:50

Are you saying this is your situation?


CREATE TABLE myTable00 (Col1 Text)
GO

INSERT INTO myTable00 (Col1) 
SELECT '0.28' UNION ALL
SELECT 'Brett'

SELECT ISNUMERIC(CONVERT(varchar(8000),Col1)) FROM myTable00

Did I mention, I hate text datatype...

Nobody (for the majortiy it seems) knows how/why to use it, AND it's a hold over from Access...

They DON'T mean the same thing....

Brett

8-)

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-11-21 : 15:42:19

If so, why not just

WHERE SUBSTRING( tac_val, 1, 4 ) between '0.01' and '0.28'

???

PaTRiCKDRD
Yak Posting Veteran

55 Posts

Posted - 2003-11-24 : 09:47:15

Many many thanks to Stoad! Thanks pal! Your where clause was the only one that worked! All the previous failed. I knew the Mid function from Visual Basic, but not Substring. I'll try to find a list of the Visual Basic functions' equivalents in SQL Server. Thanks again! And to the rest of you of course!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources