October 30, 2013 at 4:57 am
I have received this script for optimization.
I really have no clue on how can we optimize an INSERT statement, So I thought it would be good to share the issue with you.
insert into log_history (person_id, act_id, act_time, server_name, env, source_id, ip_address, width, height)
values ('z13041234560475354759057', '8452', getDate(), 'test server', 'compatible; SLCC2; .NET CLR 2.0.50727; .NET4.0C; .NET4.0E; .NET CLR 3.5.30729; .NET CLR 3.0.30729', '', '10.20.60.50', 1250, 400)
log_history is a view on my current database, from log_history table on some other database.
Table or view has no triggers. I have attached the script of same for your reference.
Regards.
October 30, 2013 at 5:24 am
T.Ashish (10/30/2013)
I have received this script for optimization.I really have no clue on how can we optimize an INSERT statement, So I thought it would be good to share the issue with you.
insert into log_history (person_id, act_id, act_time, server_name, env, source_id, ip_address, width, height)
values ('z13041234560475354759057', '8452', getDate(), 'test server', 'compatible; SLCC2; .NET CLR 2.0.50727; .NET4.0C; .NET4.0E; .NET CLR 3.5.30729; .NET CLR 3.0.30729', '', '10.20.60.50', 1250, 400)
log_history is a view on my current database, from log_history table on some other database.
Table or view has no triggers. I have attached the script of same for your reference.
Regards.
1) WHY do you want to tune it/what are you wishing to optimize for? There are multiple things that could need attention here (IO, tlog waits, latching, locking, duration). Need more details about why you or someone else thinks it needs improvement.
2) The data structures are horrible from an efficiency standpoint. One example is the person_id. Assuming they all or mostly match the layout of the given value the VARchar(24) should be a CHAR(24). More efficient structure. Also have a GUID as the ID, which is very fat - thankfully at least it is sequential.
3) However, that sequential nature can actually BECOME a bottleneck in a high-volume scenario as you run into LATCH waits as multiple inserts start stacking up trying to write to the same page.
4) Why insert into a view? Just change the app to insert into the proper table directly!? 🙂
5) SELECT a.* FROM db_image..log_history: ALWAYS prefix objects by their schema!! SELECT a.* FROM db_image.dbo.log_history for example. Also NEVER use SELECT * anywhere and ALWAYS use WITH SCHEMABINDING on views and other bindable-objects.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
October 30, 2013 at 6:31 am
Right with you til #5 Kevin. NEVER use ALWAYS and ALWAYS avoid NEVER. 😛
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
October 30, 2013 at 1:52 pm
Grant Fritchey (10/30/2013)
Right with you til #5 Kevin. NEVER use ALWAYS and ALWAYS avoid NEVER. 😛
Nice!! :w00t:
I suppose there are times to use SELECT * (other than in (NOT) EXISTS, which is perfectly fine) and maybe not SCHEMABINDING. But I probably will stick with the schema prefix - at least until someone gives me a good use case for not using it.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply