|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Squeezing out more performance!!I've got a trace and the following SQL statements are executed (with the ADO.Execute command) with every iteration of a loop (that usually runs in the 1000s): BEGIN TRANSACTION ABOTAG578322 INSERT INTO bookings_headers (id_no_primarygroup, id_no_abo, id_no_customer, booking_date) VALUES (16, 48207, 32721, '12.08.2005') insert into bookings (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date _from,date_to) values (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','', 0,8719,'06.07.2005',32721,1,'12.08.2005 10:40:53','12.08.2005 10:40:53') insert into bookings (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date _from,date_to) values (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','', 0,8719,'06.07.2005',241,2,'12.08.2005 10:40:53','12.08.2005 10:40:53') UPDATE abo_tags SET locked = '0', locking_user = NULL, lockingdate = NULL, id_no_invoice_type = 3 WHERE id_no= 578322 INSERT INTO history (date_time,item_table,user_name,item_id,item_action,description) values ('12.08.2005 10:40:53','abo_tags','Admin 1',578322,30002,'id_no_invoice_type ?2?3') COMMIT TRANSACTION ABOTAG578322 The above is a standard transaction. Some transaction can contain further inserts and updates. The above transaction takes around 30ms to perform. I think that's pretty good but I would like to squeeze out more performance. I was thinking that maybe the last 2 statements before the commit (UPDATE abo_tags and INSERT INTO history) could be moved into stored procs, but would that boost performance? Thanks, Ivan Ivan
There is no doubt that if you put this transaction in stored procedure you will benefit from performance improvenents I'd also move the UPDATE statement to the end of transaction to allow 'first' to perfom an insertion and the updating. Cannot test it in terms of perfomance for now, sorry. Show quote "Ivan Debono" <ivanm***@hotmail.com> wrote in message news:%23y%23AKwxnFHA.3936@TK2MSFTNGP10.phx.gbl... > Hi all, > > I've got a trace and the following SQL statements are executed (with the > ADO.Execute command) with every iteration of a loop (that usually runs in > the 1000s): > > BEGIN TRANSACTION ABOTAG578322 > INSERT INTO bookings_headers (id_no_primarygroup, id_no_abo, > id_no_customer, > booking_date) VALUES (16, 48207, 32721, '12.08.2005') > insert into bookings > (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date > _from,date_to) values > (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','', > 0,8719,'06.07.2005',32721,1,'12.08.2005 10:40:53','12.08.2005 10:40:53') > insert into bookings > (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date > _from,date_to) values > (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','', > 0,8719,'06.07.2005',241,2,'12.08.2005 10:40:53','12.08.2005 10:40:53') > UPDATE abo_tags SET locked = '0', locking_user = NULL, lockingdate = NULL, > id_no_invoice_type = 3 WHERE id_no= 578322 > INSERT INTO history > (date_time,item_table,user_name,item_id,item_action,description) values > ('12.08.2005 10:40:53','abo_tags','Admin > 1',578322,30002,'id_no_invoice_type > ?2?3') > COMMIT TRANSACTION ABOTAG578322 > > The above is a standard transaction. Some transaction can contain further > inserts and updates. The above transaction takes around 30ms to perform. I > think that's pretty good but I would like to squeeze out more performance. > > I was thinking that maybe the last 2 statements before the commit (UPDATE > abo_tags and INSERT INTO history) could be moved into stored procs, but > would that boost performance? > > Thanks, > Ivan > > > > It did actually slow down !!!
Show quote "Uri Dimant" <u***@iscar.co.il> schrieb im Newsbeitrag (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abotanews:e7Akd1xnFHA.3120@TK2MSFTNGP09.phx.gbl... > Ivan > > There is no doubt that if you put this transaction in stored procedure you > will benefit from performance improvenents > > I'd also move the UPDATE statement to the end of transaction to allow > 'first' to perfom an insertion and the updating. Cannot test it in terms of > perfomance for now, sorry. > > > > > > > "Ivan Debono" <ivanm***@hotmail.com> wrote in message > news:%23y%23AKwxnFHA.3936@TK2MSFTNGP10.phx.gbl... > > Hi all, > > > > I've got a trace and the following SQL statements are executed (with the > > ADO.Execute command) with every iteration of a loop (that usually runs in > > the 1000s): > > > > BEGIN TRANSACTION ABOTAG578322 > > INSERT INTO bookings_headers (id_no_primarygroup, id_no_abo, > > id_no_customer, > > booking_date) VALUES (16, 48207, 32721, '12.08.2005') > > insert into bookings > > > > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r> > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date> > _from,date_to) values (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','',> > > > 0,8719,'06.07.2005',32721,1,'12.08.2005 10:40:53','12.08.2005 10:40:53') (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota> > insert into bookings > > > > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r> > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date> > _from,date_to) values (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','',> > Show quote > > 0,8719,'06.07.2005',241,2,'12.08.2005 10:40:53','12.08.2005 10:40:53') > > UPDATE abo_tags SET locked = '0', locking_user = NULL, lockingdate = NULL, > > id_no_invoice_type = 3 WHERE id_no= 578322 > > INSERT INTO history > > (date_time,item_table,user_name,item_id,item_action,description) values > > ('12.08.2005 10:40:53','abo_tags','Admin > > 1',578322,30002,'id_no_invoice_type > > ?2?3') > > COMMIT TRANSACTION ABOTAG578322 > > > > The above is a standard transaction. Some transaction can contain further > > inserts and updates. The above transaction takes around 30ms to perform. I > > think that's pretty good but I would like to squeeze out more performance. > > > > I was thinking that maybe the last 2 statements before the commit (UPDATE > > abo_tags and INSERT INTO history) could be moved into stored procs, but > > would that boost performance? > > > > Thanks, > > Ivan > > > > > > > > > > You would need to make 3 types of sp's. One for the insert, Updates and
deletes. Or what every you need to do for each table. Then replace those adhoc statements with sp calls and ensure you use the parameters collection in ADO and set the call the type of stored procedure not text. But this is only part of the performance. THe structure of the tables, indexes etc can play a big part. Also your disk configurations. -- Show quoteAndrew J. Kelly SQL MVP "Ivan Debono" <ivanm***@hotmail.com> wrote in message news:OZNSiTznFHA.3304@tk2msftngp13.phx.gbl... > It did actually slow down !!! > > "Uri Dimant" <u***@iscar.co.il> schrieb im Newsbeitrag > news:e7Akd1xnFHA.3120@TK2MSFTNGP09.phx.gbl... >> Ivan >> >> There is no doubt that if you put this transaction in stored procedure >> you >> will benefit from performance improvenents >> >> I'd also move the UPDATE statement to the end of transaction to allow >> 'first' to perfom an insertion and the updating. Cannot test it in terms > of >> perfomance for now, sorry. >> >> >> >> >> >> >> "Ivan Debono" <ivanm***@hotmail.com> wrote in message >> news:%23y%23AKwxnFHA.3936@TK2MSFTNGP10.phx.gbl... >> > Hi all, >> > >> > I've got a trace and the following SQL statements are executed (with >> > the >> > ADO.Execute command) with every iteration of a loop (that usually runs > in >> > the 1000s): >> > >> > BEGIN TRANSACTION ABOTAG578322 >> > INSERT INTO bookings_headers (id_no_primarygroup, id_no_abo, >> > id_no_customer, >> > booking_date) VALUES (16, 48207, 32721, '12.08.2005') >> > insert into bookings >> > > (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota >> > > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r >> > > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date >> > _from,date_to) values >> > > (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','', >> > 0,8719,'06.07.2005',32721,1,'12.08.2005 10:40:53','12.08.2005 >> > 10:40:53') >> > insert into bookings >> > > (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota >> > > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r >> > > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date >> > _from,date_to) values >> > > (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','', >> > 0,8719,'06.07.2005',241,2,'12.08.2005 10:40:53','12.08.2005 10:40:53') >> > UPDATE abo_tags SET locked = '0', locking_user = NULL, lockingdate = > NULL, >> > id_no_invoice_type = 3 WHERE id_no= 578322 >> > INSERT INTO history >> > (date_time,item_table,user_name,item_id,item_action,description) values >> > ('12.08.2005 10:40:53','abo_tags','Admin >> > 1',578322,30002,'id_no_invoice_type >> > ?2?3') >> > COMMIT TRANSACTION ABOTAG578322 >> > >> > The above is a standard transaction. Some transaction can contain > further >> > inserts and updates. The above transaction takes around 30ms to >> > perform. > I >> > think that's pretty good but I would like to squeeze out more > performance. >> > >> > I was thinking that maybe the last 2 statements before the commit > (UPDATE >> > abo_tags and INSERT INTO history) could be moved into stored procs, but >> > would that boost performance? >> > >> > Thanks, >> > Ivan >> > >> > >> > >> > >> >> > > It looks straightforward, but running in Query Analyzer and examining the
Execution Plan could reveal something useful. Show quote "Ivan Debono" <ivanm***@hotmail.com> wrote in message news:%23y%23AKwxnFHA.3936@TK2MSFTNGP10.phx.gbl... > Hi all, > > I've got a trace and the following SQL statements are executed (with the > ADO.Execute command) with every iteration of a loop (that usually runs in > the 1000s): > > BEGIN TRANSACTION ABOTAG578322 > INSERT INTO bookings_headers (id_no_primarygroup, id_no_abo, > id_no_customer, > booking_date) VALUES (16, 48207, 32721, '12.08.2005') > insert into bookings > (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date > _from,date_to) values > (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','', > 0,8719,'06.07.2005',32721,1,'12.08.2005 10:40:53','12.08.2005 10:40:53') > insert into bookings > (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date > _from,date_to) values > (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','', > 0,8719,'06.07.2005',241,2,'12.08.2005 10:40:53','12.08.2005 10:40:53') > UPDATE abo_tags SET locked = '0', locking_user = NULL, lockingdate = NULL, > id_no_invoice_type = 3 WHERE id_no= 578322 > INSERT INTO history > (date_time,item_table,user_name,item_id,item_action,description) values > ('12.08.2005 10:40:53','abo_tags','Admin > 1',578322,30002,'id_no_invoice_type > ?2?3') > COMMIT TRANSACTION ABOTAG578322 > > The above is a standard transaction. Some transaction can contain further > inserts and updates. The above transaction takes around 30ms to perform. I > think that's pretty good but I would like to squeeze out more performance. > > I was thinking that maybe the last 2 statements before the commit (UPDATE > abo_tags and INSERT INTO history) could be moved into stored procs, but > would that boost performance? > > Thanks, > Ivan > > > > Did that. The first 2 insert statements take 37.5% each, the update and the
last insert statements take 12.5% each. I ran the Index Tuning Wizard and nothing new was suggested. Does this mean that there's no way to improve performance (ie. query & indexes are already optimized)? Ivan Show quote "JT" <some***@microsoft.com> schrieb im Newsbeitrag (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abotanews:uOGgS8znFHA.2156@TK2MSFTNGP09.phx.gbl... > It looks straightforward, but running in Query Analyzer and examining the > Execution Plan could reveal something useful. > > "Ivan Debono" <ivanm***@hotmail.com> wrote in message > news:%23y%23AKwxnFHA.3936@TK2MSFTNGP10.phx.gbl... > > Hi all, > > > > I've got a trace and the following SQL statements are executed (with the > > ADO.Execute command) with every iteration of a loop (that usually runs in > > the 1000s): > > > > BEGIN TRANSACTION ABOTAG578322 > > INSERT INTO bookings_headers (id_no_primarygroup, id_no_abo, > > id_no_customer, > > booking_date) VALUES (16, 48207, 32721, '12.08.2005') > > insert into bookings > > > > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r> > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date> > _from,date_to) values (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','',> > > > 0,8719,'06.07.2005',32721,1,'12.08.2005 10:40:53','12.08.2005 10:40:53') (id_no_bookings_head,id_no_primarygroup,id_no_customer,id_no_abo,id_no_abota> > insert into bookings > > > > g,id_no_bookingtype,id_no_paymentmethod,booking_month,booking_year,booking_r> > ef,amount_total,id_no_school,validity_date,id_no_payer,id_no_payer_type,date> > _from,date_to) values (ident_current('bookings_headers'),16,32721,48207,578322,1,2,'07','2005','',> > Show quote > > 0,8719,'06.07.2005',241,2,'12.08.2005 10:40:53','12.08.2005 10:40:53') > > UPDATE abo_tags SET locked = '0', locking_user = NULL, lockingdate = NULL, > > id_no_invoice_type = 3 WHERE id_no= 578322 > > INSERT INTO history > > (date_time,item_table,user_name,item_id,item_action,description) values > > ('12.08.2005 10:40:53','abo_tags','Admin > > 1',578322,30002,'id_no_invoice_type > > ?2?3') > > COMMIT TRANSACTION ABOTAG578322 > > > > The above is a standard transaction. Some transaction can contain further > > inserts and updates. The above transaction takes around 30ms to perform. I > > think that's pretty good but I would like to squeeze out more performance. > > > > I was thinking that maybe the last 2 statements before the commit (UPDATE > > abo_tags and INSERT INTO history) could be moved into stored procs, but > > would that boost performance? > > > > Thanks, > > Ivan > > > > > > > > > > No, it simply means that the Index Tuner couldn't figure out any easy
way to tweak your performance. The biggest boost you'll get is to avoid a loop (if you can). Since you're using IDENT_CURRENT to determine the last generated identity number for each table affected by this procedure, I have to assume that this is the only instance of this procedure running . If that's the case, can you not pre-determine the key values for each of your rows in your complete data-set and Insert them all at once? I work with OOP programmers, and they want to work with each object individually, which often leads to these sorts of conundrums. The more records you can insert with a single INSERT statement, the better off you'll be. Andrew's last bit of advice is a good one; what are your indexes like on each of the tables? Do you have at least a clustered index on every table, with a limited number of non-clustered indexes? What's your disk drive set up like? Is it RAID, and can you sperate your index files onto a seperate drive from your tables? What else is going on on the box while these inserts are happening? What's your average CPU utilization? Your memory space? Performance tuning takes a lot of work to narrow stuff down; it's not always a easy fix. If it takes 30ms to do those 6 statements, that's not bad. HTH, Stu  |
|||||||||||||||||||||||
Other interesting topics