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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-04-04 : 08:04:38
|
| Dave V writes "SQL 7 database.TableA has one column, 5 rows. Also has trigger (trg_A) that executes stored proc (sp_B).sp_B DELETES rows from TableB, then (using cursor <shudder> re-populates it). TableB has around 28K rows.When sp_B is run from Query Analyzer, it takes > 1 hour. When an update is done to a row on TableA (thus causing sp_B to execute), the update (and, therefore sp_B) run in < 1 minute. (!!!!!!!)I have verified that sp_B actually runs when the trigger fires. TableB gets populated correctly.How can SQL run same sp 60 times faster when trigger-fired than when run in QA? What am I doing wrong? Am I insane? Help!!?!?!?!Thanks!-dave" |
|
|
Nazim
A custom title
1408 Posts |
Posted - 2002-04-04 : 08:08:23
|
| Without actually having a look at your code, cant say much about it. the first thing post your sp with cursor ,we might help you to get a set based solution.-------------------------------------------------------------- |
 |
|
|
Jay99
468 Posts |
Posted - 2002-04-04 : 08:59:19
|
quote:
How can SQL run same sp 60 times faster when trigger-fired than when run in QA?
Unless you create and then drop some much needed index in the trigger, it probably can't.Jay<O> |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-04 : 09:53:44
|
quote:
How can SQL run same sp 60 times faster when trigger-fired than when run in QA?
Because the trigger's plan is cached after the first time it runs. If you run the same statement in QA twice or more in a row, in all likelihood you'll see a vast improvement in execution time, because the plan (and the data) will be cached.quote:
Am I insane?
No   more than I am! WOO HOO! HAHAHAHAHAHAHAHAHAHAHA! WEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEEE! |
|
|
|
Dave Vaughn
Starting Member
2 Posts |
Posted - 2002-04-04 : 11:05:47
|
| I've been asked to submit the code for "sp_B". I must say first, however, that this code is not ours. It was inherited from a 3rd party commercial product vendor whose expertise obviously lies somewhere other than SQL. I've already re-written this as a single INSERT...SELECT statement that runs in about 15 seconds, so there's no real pressure to solve the mystery. However, I'd LOVE to know how there can be such a huge discrepancy between code run from QA and the SAME CODE run from a trigger. I agree w/ Robvolk that caching can have some affect, but this much? Anyway, here's the code in sp_B (the table "TableB" in my example is named "qrylastaction"): DELETE FROM qrylastaction /* ******************************* */ /* Declare all the local variables */ /* ******************************* */ DECLARE @v_grpST_ap_num VARCHAR(15), @v_grpSD_hmda_act VARCHAR(2), @v_grpMaxOfST_dt_key VARCHAR(8), @dt_grpMaxOfST_date DATETIME, @v_grpLastOfSD_status VARCHAR(10), @v_grpLastOfST_time VARCHAR(4), @i_grpNumberOfRecords INTEGER /* ****************************************************************************** */ /* Create a temprory table that stores ST_ap_num from MSXSTAT, SD_hmda_act from */ /* MSXSTDEF the maximum value of MSXSTAT.ST_dt_key for each ST_ap_num,SD_hmda_act,*/ /* the maximum value of MSXSTAT.ST_date for each ST_ap_num,SD_hmda_act and the */ /* count of number of records for each ST_ap_num,SD_hmda_act */ /* ****************************************************************************** */ SELECT MSXSTAT.ST_ap_num AS ST_ap_num, MSXSTDEF.SD_hmda_act AS SD_hmda_act, Max(MSXSTAT.ST_dt_key) AS MaxOfST_dt_key, Max(MSXSTAT.ST_date) AS MaxOfST_date, COUNT(*) AS NumberOfRecords INTO #LastActionGroup FROM MSXSTDEF RIGHT JOIN MSXSTAT ON MSXSTDEF.SD_status = MSXSTAT.ST_status GROUP BY MSXSTAT.ST_ap_num, MSXSTDEF.SD_hmda_act HAVING MSXSTDEF.SD_hmda_act BETWEEN '1' AND '6' /* **************************************************************** */ /* The following cursor is to find the last value of SD_status and */ /* ST_time for each ST_ap_num, SD_hmda_act.The cursor will contain */ /* SD_status and ST_time for all ST_ap_num, SD_hmda_act */ /* **************************************************************** */ DECLARE cur_LastAction SCROLL CURSOR FOR SELECT MSXSTAT.ST_time, MSXSTDEF.SD_status FROM #LastActionGroup, MSXSTDEF RIGHT JOIN MSXSTAT ON MSXSTDEF.SD_status = MSXSTAT.ST_status WHERE MSXSTAT.ST_ap_num = #LastActionGroup.ST_ap_num AND MSXSTDEF.SD_hmda_act = #LastActionGroup.SD_hmda_act OPEN cur_LastAction /* ******************************************************* */ /* The following loop is to process all the records in the */ /* temprory table one by one */ /* ******************************************************* */ WHILE EXISTS(SELECT 'x' FROM #LastActionGroup) BEGIN SET ROWCOUNT 1 /* ************************************************* */ /* Select the column values for each record from the */ /* temprory table to temprory fields */ /* ************************************************* */ SELECT @v_grpST_ap_num = ST_ap_num, @v_grpSD_hmda_act = SD_hmda_act, @v_grpMaxOfST_dt_key = MaxOfST_dt_key, @dt_grpMaxOfST_date = MaxOfST_date, @i_grpNumberOfRecords = NumberOfRecords FROM #LastActionGroup DELETE #LastActionGroup SET ROWCOUNT 0 /* ***************************************************************** */ /* Fetch SD_status, ST_time values for the last record for each */ /* group into local variables */ /* ***************************************************************** */ FETCH RELATIVE @i_grpNumberOfRecords FROM cur_LastAction INTO @v_grpLastOfST_time, @v_grpLastOfSD_status /* ************************* */ /* Insert into qrylastaction */ /* ************************* */ INSERT qrylastaction VALUES (@v_grpST_ap_num, @v_grpSD_hmda_act, @v_grpMaxOfST_dt_key, @v_grpLastOfST_time, @v_grpLastOfSD_status, @dt_grpMaxOfST_date) END /* ******************************* */ /* Close and Deallocate the cursor */ /* ******************************* */ CLOSE cur_LastAction DEALLOCATE cur_LastAction SELECT * INTO #temp FROM qrylastaction ORDER BY ST_ap_num, MaxOfST_dt_key DESC DELETE qrylastaction INSERT qrylastaction SELECT * FROM #temp END /* End #1 */ |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-04-04 : 11:42:26
|
Here's some numbers to show how much faster cached data can be:A FAST, VERY FAST, hard drive has an average seek time of 6-8 milliseconds (thousandths of a second). So you cannot get any data from the hard drive in less than 8 ms. Plus the data transfer is limited by the drive interface; for IDE it maxes out at about 100-133 MB per second, for SCSI, up to 160 MB for most installations. Even these numbers are very optimistic, because they involve the most optimal data transfer possible: a straight dump into the bus of a single, continuous read from the disk (this NEVER happens, EVER, in the real world...you will be struck by lightning AND win the lottery in the same day before you ever see this kind of disk performance!)SDRAM running on a 100MHz bus can be accessed in 10 nanoseconds (BILLIONTHS of a second). This is almost 1,000,000 times faster than the hard drive. And there is NO lag, and no bus contention (well, maybe a little) for RAM access. Only older servers would even run at this bus speed; 133 MHz and higher have been around for at least 3 years now. For each cycle, you are effectively transferring one byte of data (100 MB per second). If you are using DDR, you can access twice as much RAM in the same amount of time (not faster, just more) If you're using RDRAM, it runs at the system bus speed, which is a minimum of 400 MHz in those systems. That's 2.5 nanoseconds AT MOST; almost 4,000,000 times faster than the hard drive, and theoretically up to 400 MB per second. And RAM is only getting faster!Therefore, a 60-fold improvement (!) isn't as spectacular as it might seem. The reason it's much less than theory is because there are still many CPU cycles associated with the process (data is shuffled between RAM, processor cache, and registers too) Data retrieval is only part of it, BUT that process is only as fast as the slowest part. If you can keep disk access to a minimum, or eliminate it, you've eliminated a portion that is 1,000,000 times slower than the rest (ever go to Disneyworld with your grandparents???  )One other thing about cursor performance: anytime you use a cursor, you effectively circumvent the query optimizer and the query plan it generates. So if the optimizer finds an index that does all the work for you, it's ignored because the cursor has to traverse the table. The best you can hope for is that all the cursor's data will end up in cache, but that is unlikely with large result sets, and you're still left with the overhead of cursor positioning and fetching.Gimmee some time and I know I can rewrite this without a cursor and that someone will beat me to it! |
|
|
|
Dave Vaughn
Starting Member
2 Posts |
Posted - 2002-04-04 : 12:31:12
|
| Thanks for the detailed reply.First of all, don't expend any effort on a cursor-less solution -- I've already done one. (That was the first thing I did -- I'm not exactly the biggest fan of cursors; in many many cases, they're just the result of people trained in classical top-down structured methods who don't want to -- or can't -- adapt to the set-based methods of SQL)2 thoughts on your memory-vs-disk ruminations:1) This is running an a Quad-something with about 2GB of RAM devoted to SQL Server, plus the database resides on an EMC server farm connected with the widest pipe available (not sure of specs) and plenty of its own RAM for caching. Bottom line is that there ought to be plenty of other caching going on. Maybe not enough, but it should still be better than a "standard" server (standalone, 1 CPU, less RAM, internal SCSI drives, etc.). Or maybe not.2) If that's the case, why not run ALL of our code from a trigger? As long as not too much is running at once, wouldn't we see similar performance gains? I know the answer, but want to hear your thoughts. |
|
|
|
Onamuji
Aged Yak Warrior
504 Posts |
Posted - 2002-04-04 : 15:27:28
|
| i would just like to mention that I'm in love with EMC and SAN drives.... thank you all and have a good day! and no it's not because 2 years ago the vp's of EMC got me trashed after a convention... ;-)Edited by - onamuji on 04/04/2002 15:28:09 |
|
|
|
|
|
|
|
|
Triggers superfast?