Executing a procedure for every selected record

In a few places in my application I want to execute a procedure for every selected record. The only way that I know how to do this is to use a cursor, as below. This code works perfectly, but everything I read says that one should use set operations rather than cursors wherever possible, as they are much more efficient. So what I really want to do is something like
Exec procedure argument [,argument]... where argument in (Selet value from ...)
or perhaps
Exec procedure (select ...) [,argument]
or
SELECT (procedure...

all of which are invalid. Is there any syntax that avoids the cursor in: -

--Copy all facts (and their details, if any) to the new INDIDeclare ccopyIndicursor for--Step through factsSelect Factidfrom gdbfactwhere factindiid = @.IndiidFromOpen ccopyIndiFETCH Next from ccopyIndiinto @.Factidwhile@.@.Fetch_status = 0Beginexec dbo.gdbcopyfact @.NewIndiid, @.FactidFetch next from ccopyIndiinto @.FactidEndCLOSE ccopyIndiDEALLOCATE ccopyIndi

(BTW, dbo.gdbcopyfact is NOT a simple INSERT statement)

The is one of the occasions where you really have to use a cursor. The other option is to create a new SP (gdbcopyfactbyfactindiid) which performs a set based copy (can't say if that's possible without seeing the code for gdbcopyfact)

|||

Let say that, the dbo.gdbcopyfact stored procedure having an Insert statment where you insert the values of the two parameters you passed to the SP.

So, just do this:

12-- Example34INSERT INTO MyTable5SELECT value1, value2 FROM SourceTable6GO

Where,

MyTable: is the table that you insert data in inside the stored procedure (SP).

SourceTable: repersents the gdbfact table in your example.

value1: represent the first column name that you get its value in the cursor's select.

value2: represent the second column name that you get its value in the cursor's select.

and so on for other columns values (if needed).

This will do the job in one time with only two line of codes!

|||

But, since Robert said "dbo.gdbcopyfact is NOT a simple INSERT statement", it's not necessarily that simple.

|||

Robert: If you have to do a certin operation on each of the records in the table, then the cursor is the only option you have (as I know).

If not, then the sample I posted eairler will do the job just fine.

Regards,
CS4Ever

|||

Thank you Gunteman, I thought that would be the answer, but at least now I'm certain that I haven't missed anything. As you commented in response to CS4Ever's post, if the procedure HAD been a simple insert then I would have known what to do, but I made a point of saying that it wasn't. Also, I was looking for a general approach that will work in this and other situations.

If anybody from the SQL design team is listening, what I'd REALLY like to see in some future release is
SELECT ...... BEGIN
...
END [SELECT]
with the rather obvious semantics that the statements between SELECT ... BEGIN and END SELECT are executed for each record. This should be a relatively easy extension to T-SQL that would not invalidate any previous programs. This (except that the keyword was DO instead of BEGIN) was syntax included in a 4GL that we developed ~ 20 years ago (PL/I, mainframe), and it worked very well. An alternative that is also compatible with current T-SQL syntax is
WHILE SELECT ....
...
END [WHILE]

Aesthetically I prefer the first option.

Thank you

sql

ExecuteScalarProblem

 In my application Im inserting data into the database and returning the ID of the new record, to do this I have the following stored procedure: 

 
ALTER PROCEDURE Turbo_InsertAppChange(@.app_namevarchar(50),@.app_developerchar(3),@.app_rq_numchar(10),@.app_completition_datedatetime, @.app_descriptionvarchar(1500))AS SET NOCOUNT ON INSERT INTO Turbo_Change_Log(app_name,app_developer,app_rq_num,app_completition_date,app_description,date_entered)SELECT @.app_name, @.app_developer, @.app_rq_num,CONVERT(DATETIME,@.app_completition_date), @.app_description,GETDATE()SELECT SCOPE_IDENTITY()
 Where I use this procedure I have this code:  

 
Protected Sub EnterAppInfo()'##INSERT APP INFO## sSQL ="Turbo_InsertAppChange" Command =New SqlCommand(sSQL, Connection) Command.CommandType = CommandType.StoredProcedure Command.Parameters.Add("@.app_name", SqlDbType.VarChar).Value = application_name.Text.ToString Command.Parameters.Add("@.app_developer", SqlDbType.Char).Value = developer_list.SelectedValue.ToString Command.Parameters.Add("@.app_rq_num", SqlDbType.VarChar).Value = rq_num.Text.ToString Command.Parameters.Add("@.app_completition_date", SqlDbType.DateTime).Value =CType(Api_calendar1.DDate,Date) Command.Parameters.Add("@.app_description", SqlDbType.VarChar).Value = proj_desc.Text.ToStringTry Connection.Open()Dim NewIdAs Integer =CType(Command.ExecuteScalar(),Integer)
 

...

But I'm getting theObject reference not set to an instance of an object error at theDim NewId As Integer = CType(Command.ExecuteScalar(),Integer) line. When I run this procedure alone in QueryAnalyzer it returns the ID like its supposed to, but when I run it in my application I get the above error. What am I doing wrong here?

ExecuteScalar return null reference if the result set is empty.

So you convert a null object to integer which cause the error:

CType(Command.ExecuteScalar(),Integer)

You can catch the exeption and return 0(means null).

ExecuteNonQuery error

When I try to insert a record with the ExecuteNonQuery command, I get the following error information. Any clues why? Thanks.

SSqlException was unhandled by user code
...
Message="Incorrect syntax near [output of one of my field names]."
...
[Item detail:] In order to evaluate an indexed property, the property must be qualified and the arguments must be explicitly supplied by the user.

My code:

Private objCmdAs SqlCommand
Private strConnAsNew SqlConnection(ConfigurationManager.AppSettings("conn"))
...
objCmd =New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _
"VALUES('" & strUser &"','" & strFName.Text &"','" & strLName.Text &"', '" & strLang.Text &"', '" & strCtry.Text &"', '" & strPhone.Text &"'" _
, strConn)
strConn.Open()
objCmd.ExecuteNonQuery()

hi muybn,

there's not closing bracket for values() i mean

objCmd =New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _
"VALUES('" & strUser &"','" & strFName.Text &"','" & strLName.Text &"', '" & strLang.Text &"', '" & strCtry.Text &"', '" & strPhone.Text &"')" _ 'can u see please i added a bracket )
, strConn)

regards,

satish.

|||Don't concatenate UI-supplied data to SQL statements that will be executed. This is an insecure practice as it opens up your server to SQL injection attacks. Use parameters instead.|||

Thanks, but doesn't the closing parenthesis bracket go after the reference to the connection string, in this case on the last line, strConn)?

|||

Thanks, TMorton. Is this merely a security precaution or would it cause the error I'm experiencing?

I plan to incorporate parameters into my project before I take it live. Can you point me to a definitive tutorial source for forming parameters, or better yet, mock up some of the variables that I've supplied above into parameters? To be honest, I've looked at quite a few sites and they've all confused me with how to define the parameters after the SQL statement, where you set the parameters equal to the variables.

|||

What Terri is recommending is

1) considered a best practice
2) offers protection from sql injection
3) avoids issues with getting your quotes correct when concatenating the sql. (have you considered what happens if a lastname is "O'Rourke")

Dim objCmdAs SqlCommandDim strConnAs New SqlConnection(ConfigurationManager.AppSettings("conn"))'... objCmd.Parameters.Add(New SqlParameter("@.p1", strUser)) objCmd.Parameters.Add(New SqlParameter("@.p2", strFName.Text)) objCmd.Parameters.Add(New SqlParameter("@.p3", strLName.Text)) objCmd.Parameters.Add(New SqlParameter("@.p4", strLang.Text)) objCmd.Parameters.Add(New SqlParameter("@.p5", strCtry.Text)) objCmd.Parameters.Add(New SqlParameter("@.p6", strPhone.Text)) objCmd =New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _" VALUES(@.p1,@.p2,@.p3,@.p4,@.p5,@.p6)", strConn) strConn.Open() objCmd.ExecuteNonQuery()

|||

still good option is write stored procedures wherever necessary, they are better in performance as they are precompiled. rest what mike has given as example is good one.

and for earlier post values clause has its own brackets so you need to close where i mentioned earlier.

thanks,

satish

|||Thanks, now I see. Hopefully it will work now.|||

satish_nagdev:

still good option is write stored procedures wherever necessary, they are better in performance as they are precompiled.

Performance differences between dynamic sql and stored procs is one of those things that is widely disputed. Personally i'm quite fond of dynamic sql but will still use a sproc if i see a benefit. But, rather than just debate the issue, let's test it. Here I offer the results of a very simple performance test.

IterationDynSqlSproc10.0003820.00023520.0001760.00017430.0001490.00016440.0001390.00015150.0001650.00016860.0001420.00015970.0001410.00015080.0001610.00016790.0001430.000159100.0001410.000150

Since the execution plan for dynamic sql is also cached (as is the execution plan for a sproc), the dynamic sql actually turns out to be quite performant.
Note that on iteration 1, the dynamic sql suffered a little because i ran it first. If i had run the sproc first, the result would look more like this:

IterationDynSqlSproc10.0001850.00038320.0001430.00016030.0001400.00015340.0004810.00017250.0001580.00020460.0001380.00015370.0001390.00014880.0001510.00017290.0001440.000149100.0001400.000147

Both set of results were taken after running my test code a few times to try to be more consistent with how a system in motion might perform.

Of course test results mean nothing unless you know how the test was run. I ran the test on my development system where sql 2000 was also installed on the same box.

This is the test code. Please adapt it to your own real word test to see if dynamic sql can compete with your own sprocs.

The test sproc:

CREATE PROCEDURE GetUserActivity (@.userid integer)AS-- tblTransactionLog has 1 million+ rows of data-- the userid column is indexedSELECT *FROM tblTransactionLogWHERE userid = @.userId;GO

The page code:

Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadDim swAs StopwatchDim drAs SqlDataReaderDim connAs SqlConnectionDim cmdDynamicAs New SqlCommandDim cmdSprocAs New SqlCommandDim dynParamAs SqlParameterDim sprocParamAs SqlParameterDim tsDynamicAs TimeSpanDim tsSprocAs TimeSpan conn =New SqlConnection("Initial Catalog=webcommon;Integrated Security=True") dynParam =New SqlParameter("@.userid", SqlDbType.Int, 4) dynParam.Value = 4347 cmdDynamic.Parameters.Add(dynParam) sprocParam =New SqlParameter("@.userid", SqlDbType.Int, 4) sprocParam.Value = 4347 cmdSproc.Parameters.Add(sprocParam) conn.Open() Using conn'prepare for sproc cmdDynamic.CommandText ="GetUserActivity" cmdDynamic.CommandType = CommandType.StoredProcedure cmdDynamic.Connection = conn'prepare for dynsql cmdSproc.CommandText ="select * from tblTransactionLog where userid = @.userid;" cmdSproc.CommandType = CommandType.Text cmdSproc.Connection = conn Response.Write("<table border=""1""><tr><th>Iteration</th><th>DynSql</th><th>Sproc</th></tr>")For indexAs Integer = 1To 10'going first incurs a small performance penalty on the very first iteration sw = Stopwatch.StartNew dr = cmdSproc.ExecuteReader() tsSproc = sw.Elapsed dr.Close() sw = Stopwatch.StartNew dr = cmdDynamic.ExecuteReader() tsDynamic = sw.Elapsed dr.Close() Response.Write(String.Format("<tr><td>{0}</td><td>{1}</td><td>{2}</td></tr>", index, tsDynamic.TotalSeconds.ToString("n6"), tsSproc.TotalSeconds.ToString("n6")))Next Response.Write("</table>")End UsingEnd Sub

|||

satish_nagdev:

still good option is write stored procedures wherever necessary, they are better in performance as they are precompiled.

This is misguided advice. There are good reasons to use stored procedures, but performance is not one of them. There are places where *not* using stored procedures is a better option. This topic (stored procedures vs. inline SQL) is the subject of a lot of heated, well-reasoned discussion in the blogosphere.

|||How awesome that you would take the time to detail all this for me! Thanks. I will test it out soon. Right now, I have to go one step at a time understanding the underlying principles and solving some other errors that are showing up.|||Mike, I'm getting this error while trying to use your suggestion on parameters: "Object reference not set to an instance of an object." This comes with each line that begins with "objCmd.Parameters." These are merely strings, as far as I can see, so I don't know why it would be asking for object instances.|||

my bad. when adapting your code i got it out of sequence...you need to create the command object before you add the parameters.

Dim objCmdAs SqlCommandDim strConnAs New SqlConnection(ConfigurationManager.AppSettings("conn")) objCmd =New SqlCommand("INSERT INTO tblUsers (UserID,FName,LName,PrimLang1,Ctry,Phone)" & _" VALUES(@.p1,@.p2,@.p3,@.p4,@.p5,@.p6)", strConn) objCmd.Parameters.Add(New SqlParameter("@.p1", strUser)) objCmd.Parameters.Add(New SqlParameter("@.p2", strFName.Text)) objCmd.Parameters.Add(New SqlParameter("@.p3", strLName.Text)) objCmd.Parameters.Add(New SqlParameter("@.p4", strLang.Text)) objCmd.Parameters.Add(New SqlParameter("@.p5", strCtry.Text)) objCmd.Parameters.Add(New SqlParameter("@.p6", strPhone.Text)) strConn.Open() objCmd.ExecuteNonQuery()

|||

Terri, Mike,

i wont argue on that. I agree with you guys upto a limit, but mike in my last project there were heaps of inline queries we found while re-writing the application, so using procedures added positively to scalability. so depends on from situation to situation.

mike you've done testing thats good, if you get time could you do it for simultaneous instances say 10 at a go?

thanks,

satish.

|||

Inline queries should by managed in a DAL Component. My DAL is a seperate project which keeps things nice and tidy.
I actually have very few hand typed dynamic sql statements. My dynamic sql is about 99% generated on the fly.

Anyways, I put my test page through an ACT test script and here are the results. I used only 8 simultaneous connections to avoid a resultset with http errors .

Test 1 - sproc performance:

commented out the dynamic reader code inside the loop
test duration: 1 minute
Avg Requests per second: 587
Total requests completed: 35,232

Test 2 - dynamic sql performance:

commented out the sproc reader code inside the loop
test duration: 1 minute
Avg Requests per second: 601
Total requests completed: 36,082

My test setup is a little flawed since my test script was running on the same system that was under test. But, since we're just doing a head to head comparison and since both tests were subject to the same testing flaw, i'd have to conclude that dynamic sql (in this specific test case) outperformed a stored proc.

Execute SQL from File: How Can I process the record set?

I want to be able to pass the location of a file (contains SQL to be executed) to my package at run time. To do this I was going to override the connection string for the file.

I've created a 'Execute SQL Task' that opens the sql script and stores the full result set into an variable (system.object). I can execute this and it works fine i.e turns green :).

However I can't work out how to get the data back out of the variable. I have found a doc on SQLIS (The ExecuteSQL Task) that explained how to get the data in to a variable but didn't tell me how to process the data afterwards. There is another article on there that shows how to shred a recordset (Shredding a Recordset) but this example uses an OLE DB source and the 'Recordset Destination' object. This would work but but the only options are sql from a variable or the option to type in the command.

I really have two questions here.

1. Using the first method how can I pass the data stored in the variable into a data flow so that I can use it.

2. using the second method, how can I pass the SQL into a variable at runtime from a file?

Has anyone got examples of how they read SQL from a file and process the data without having to hard code the sql or sql file name in the package.

#1) If you have data in an object and want to use it in a data-flow then you're going to need to loop over the records in the object and add them to the pipeline. Its custom source adapter time!!!

Here's how you do it in a script task: http://blogs.conchango.com/jamiethomson/archive/2005/02/08/960.aspx I dare say you can take this code and adapt it to use it in a script component. I have to say, I haven't actually tried it.

#2) Again you may need a custom/script task to do this. I don't have time to look at this now but I'll try later. It shouldn't be too difficult, just use System.IO namespace.

-Jamie|||To configure the file connection at runtime. You need to use expressions. On the properties of the data flow task select expressions. In here you can set the connection string property of the flat file to the variable (containing a filename).

Execute only and don't return results

Is there an option in SQL Server to just execute a query but don't return
it's results? The aim is to run a batch of queries to record the number of
reads they make, without having to return data back to the client, which
will take some time.
Thanks in advance.
Regards
Ray MondCheck the SET NOCOUNT option.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Is there an option in SQL Server to just execute a query but don't return
> it's results? The aim is to run a batch of queries to record the number

---

of

quote:

---

> reads they make, without having to return data back to the client, which
> will take some time.
> Thanks in advance.
> --
> Regards
> Ray Mond
>

---

|||That only turns off the 'x rows affected' message. I know of the SET
ROWCOUNT option, but would that affect the execution plan in any way? I
want the query to run to completion, I just don't want the results returned.
Regards
Ray Mond
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:e4ZsCBa2DHA.2160@.TK2MSFTNGP12.phx.gbl...

quote:

---

> Check the SET NOCOUNT option.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Ray Mond" <yeohray@.hotmail.com> wrote in message
> news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
return[QUOTE]
> of
>

---

|||Using SET ROWCOUNT could be dangerous, since execution will stop after that
many rows are reached.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:eFyBi5a2DHA.2700@.tk2msftngp13.phx.gbl...

quote:

---

> That only turns off the 'x rows affected' message. I know of the SET
> ROWCOUNT option, but would that affect the execution plan in any way? I
> want the query to run to completion, I just don't want the results

---

returned.

quote:

---

> --
> Regards
> Ray Mond
> "SriSamp" <ssampath@.sct.co.in> wrote in message
> news:e4ZsCBa2DHA.2160@.TK2MSFTNGP12.phx.gbl...
> return
number[QUOTE]
which[QUOTE]
>

---

|||you might select count(*) instead of the other table columns... The client
will only get the number of rows that were selected.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...

quote:

---

> Is there an option in SQL Server to just execute a query but don't return
> it's results? The aim is to run a batch of queries to record the number

---

of

quote:

---

> reads they make, without having to return data back to the client, which
> will take some time.
> Thanks in advance.
> --
> Regards
> Ray Mond
>

---

|||I can't do this because this will affect the execution plan.
Regards
Ray Mond
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23$F2Dnc2DHA.2208@.TK2MSFTNGP12.phx.gbl...

quote:

---

> you might select count(*) instead of the other table columns... The client
> will only get the number of rows that were selected.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Ray Mond" <yeohray@.hotmail.com> wrote in message
> news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
return[QUOTE]
> of
>

---

|||[posted and mailed, please reply in news]
Ray Mond (yeohray@.hotmail.com) writes:

quote:

---

> Is there an option in SQL Server to just execute a query but don't
> return it's results? The aim is to run a batch of queries to record the
> number of reads they make, without having to return data back to the
> client, which will take some time.

---

The best way is probably to insert the data into a table. Of course,
that will incur the cost of writing to disc, but that is probably
cheaper than to return to the client. At least you will get more
consistent performance, since you would not depend on network performance.
But you need to make sure that the database you are insering data into
is big enough, so that you results does not get distorted by auto-grow.
It may be more convenient to use SELECT INTO, than a pre-created table,
but creating a table SELECT INTO is more expensive than CREATE TABLE
and may cause some hundreds of reads on its own.
Whatever, don't use a table variable, because this could affect the
query plan, since you cannot get parallelism when you insert into a
table variable.
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Execute only and don't return results

Is there an option in SQL Server to just execute a query but don't return
it's results? The aim is to run a batch of queries to record the number of
reads they make, without having to return data back to the client, which
will take some time.
Thanks in advance.
--
Regards
Ray MondCheck the SET NOCOUNT option.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> Is there an option in SQL Server to just execute a query but don't return
> it's results? The aim is to run a batch of queries to record the number
of
> reads they make, without having to return data back to the client, which
> will take some time.
> Thanks in advance.
> --
> Regards
> Ray Mond
>|||That only turns off the 'x rows affected' message. I know of the SET
ROWCOUNT option, but would that affect the execution plan in any way? I
want the query to run to completion, I just don't want the results returned.
--
Regards
Ray Mond
"SriSamp" <ssampath@.sct.co.in> wrote in message
news:e4ZsCBa2DHA.2160@.TK2MSFTNGP12.phx.gbl...
> Check the SET NOCOUNT option.
> --
> HTH,
> SriSamp
> Please reply to the whole group only!
> http://www32.brinkster.com/srisamp
> "Ray Mond" <yeohray@.hotmail.com> wrote in message
> news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> > Is there an option in SQL Server to just execute a query but don't
return
> > it's results? The aim is to run a batch of queries to record the number
> of
> > reads they make, without having to return data back to the client, which
> > will take some time.
> >
> > Thanks in advance.
> >
> > --
> > Regards
> > Ray Mond
> >
> >
>|||Using SET ROWCOUNT could be dangerous, since execution will stop after that
many rows are reached.
--
HTH,
SriSamp
Please reply to the whole group only!
http://www32.brinkster.com/srisamp
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:eFyBi5a2DHA.2700@.tk2msftngp13.phx.gbl...
> That only turns off the 'x rows affected' message. I know of the SET
> ROWCOUNT option, but would that affect the execution plan in any way? I
> want the query to run to completion, I just don't want the results
returned.
> --
> Regards
> Ray Mond
> "SriSamp" <ssampath@.sct.co.in> wrote in message
> news:e4ZsCBa2DHA.2160@.TK2MSFTNGP12.phx.gbl...
> > Check the SET NOCOUNT option.
> > --
> > HTH,
> > SriSamp
> > Please reply to the whole group only!
> > http://www32.brinkster.com/srisamp
> >
> > "Ray Mond" <yeohray@.hotmail.com> wrote in message
> > news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> > > Is there an option in SQL Server to just execute a query but don't
> return
> > > it's results? The aim is to run a batch of queries to record the
number
> > of
> > > reads they make, without having to return data back to the client,
which
> > > will take some time.
> > >
> > > Thanks in advance.
> > >
> > > --
> > > Regards
> > > Ray Mond
> > >
> > >
> >
> >
>|||you might select count(*) instead of the other table columns... The client
will only get the number of rows that were selected.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Ray Mond" <yeohray@.hotmail.com> wrote in message
news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> Is there an option in SQL Server to just execute a query but don't return
> it's results? The aim is to run a batch of queries to record the number
of
> reads they make, without having to return data back to the client, which
> will take some time.
> Thanks in advance.
> --
> Regards
> Ray Mond
>|||I can't do this because this will affect the execution plan.
--
Regards
Ray Mond
"Wayne Snyder" <wsnyder@.computeredservices.com> wrote in message
news:%23$F2Dnc2DHA.2208@.TK2MSFTNGP12.phx.gbl...
> you might select count(*) instead of the other table columns... The client
> will only get the number of rows that were selected.
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Computer Education Services Corporation (CESC), Charlotte, NC
> www.computeredservices.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
>
> "Ray Mond" <yeohray@.hotmail.com> wrote in message
> news:O$l1kcY2DHA.2680@.TK2MSFTNGP11.phx.gbl...
> > Is there an option in SQL Server to just execute a query but don't
return
> > it's results? The aim is to run a batch of queries to record the number
> of
> > reads they make, without having to return data back to the client, which
> > will take some time.
> >
> > Thanks in advance.
> >
> > --
> > Regards
> > Ray Mond
> >
> >
>|||[posted and mailed, please reply in news]
Ray Mond (yeohray@.hotmail.com) writes:
> Is there an option in SQL Server to just execute a query but don't
> return it's results? The aim is to run a batch of queries to record the
> number of reads they make, without having to return data back to the
> client, which will take some time.
The best way is probably to insert the data into a table. Of course,
that will incur the cost of writing to disc, but that is probably
cheaper than to return to the client. At least you will get more
consistent performance, since you would not depend on network performance.
But you need to make sure that the database you are insering data into
is big enough, so that you results does not get distorted by auto-grow.
It may be more convenient to use SELECT INTO, than a pre-created table,
but creating a table SELECT INTO is more expensive than CREATE TABLE
and may cause some hundreds of reads on its own.
Whatever, don't use a table variable, because this could affect the
query plan, since you cannot get parallelism when you insert into a
table variable.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp