August 11, 2010 at 8:48 am
I do the SQL Support on what is the worst written app I have seen in 30 years in the business. It's a niche market app, but big in their field but written by people who have almost no understanding of SQL Server. It's a finance services app (10 years old) that still requires manual intervention in the raw data, on almost a daily basis to fix data. Their support people have one answer to most issues; Have the indexes been de-fraged? Anyway
Profiling their code I see a lot of
declare @p1 int
set @p1=-1
exec sp_prepare @p1 output,NULL,N'SELECT * FROM Images',1
select @p1
My understanding of sp_prepare is to force SQL to prepare a Query Plan and return the plan handle, that then gets pass to the query engine with a sp_execute statement.
In the trace, the is almost never a matching sp_execute statement
or if there is it looks like sp_execute N'SELECT * FROM Images' where cid = 225
no handle passed, mostly likely a good thing since I believe the query plan for a "Select * from anything" is a scan.
They do this thousands of times a day and it appears as just wasted resources in sql. But I haven't actively programmed in a few years, so wanted to check my interruptions. Thanks for any feedback. I should stated, they use NO stored procedures.
mark
June 22, 2011 at 6:23 am
Don't worry about sp_prepare command. it doesn't affect your system.You can check from client statistics
create table testPre(a uniqueidentifier,b uniqueidentifier,c uniqueidentifier,d uniqueidentifier,e uniqueidentifier)
go
insert into testPre values(NEWID(),NEWID(),NEWID(),NEWID(),NEWID())
go 20000
--Press Shift+Alt+S to open client statistis
go
--trial 1 query
select * from testPre
go
--trial 2 query
declare @commandtext nvarchar(max)=N'SELECT * FROM testPre'
declare @p1 int
set @p1=-1
exec sp_prepare @p1 output,NULL,@commandtext,1
select @p1
go
--compare trial1 and trial2
drop table testPre
June 23, 2011 at 1:37 am
It's entirely possible they're not actually doing this themselves--it could be being done by the language libraries they're using; I've seen a VB6 application do something very similar when reading data from a SQL database.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply