Is there performance penalty for returning resultsets instead of rows

> Does anyone know if there's significant overhead compared to single result
> set? Pages won't be larger than 30-40 rows so maybe that's not important
> at all?
I wasn't sure about the overhead so created the following procs.
CREATE PROC Proc1 AS
SELECT * FROM
(SELECT 1 AS Test
UNION ALL SELECT 2
UNION ALL SELECT 3
<snip>
UNION ALL SELECT 40) As Test
GO
CREATE PROC Proc2 AS
SELECT 1
SELECT 2
SELECT 3
<snip>
SELECT 40
GO
I then ran a test of 10,000 iterations with the code below. The app and SQL
were on the same machine:
private void AdHocTest()
{
_connection.Open();
SqlCommand command = new SqlCommand();
command.CommandType = CommandType.StoredProcedure;
command.Connection = _connection;
command.CommandText = "dbo.Proc1";
RunTest(connection, command, 10000);
command.CommandText = "dbo.Proc2";
RunTest(connection, command, 10000);
_connection.Close();
}
private void RunTest(SqlConnection connection,
SqlCommand command,
int iterations)
{
DateTime startTime = DateTime.Now;
for (int i = 0; i < iterations; ++i)
{
//execute command and consume all results
SqlDataReader reader = command.ExecuteReader();
do
{
while (reader.Read()) ;
}
while (reader.NextResult());
reader.Close();
}
System.Diagnostics.Trace.WriteLine(
string.Format("Test {0} duration is {1}",
command.CommandText,
DateTime.Now.Subtract(startTime).ToString()));
}
The single result method was 1 second for all 10,000 iterations and the
multiple result method was about 2 seconds. I would expect an even more
pronounced difference with SQL on a separate box.
Of course, this test didn't include the overhead of the server cursor or
client processing. In your actual application, the performance difference
probably won't matter unless you have a lot of users.
Note that you might run into issues with a single 'generic' paging solution.
There are many different pagination techniques and no single one is best for
all situations.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
news:OKkF9y3aGHA.4520@.TK2MSFTNGP03.phx.gbl...
> I'm using server cursor for generic paging.
> Interesting part is this:
> FETCH RELATIVE @.StartRow FROM cur
> WHILE @.PageSize > 1 AND @.@.FETCH_STATUS = 0
> BEGIN
> FETCH NEXT FROM cur
> SET @.PageSize = @.PageSize - 1
> END
> Instead of single result set with N rows, this returns N result sets with
> 1 row. I can handle that in my client, that's not an issue.
> Does anyone know if there's significant overhead compared to single result
> set? Pages won't be larger than 30-40 rows so maybe that's not important
> at all?
> Thanks,
> Dejan> Since I'm making admin utility I also thought to show row count for each
> table in a database but I'm not sure about that now.
If a count of the number of rows is not needed for your paging technique,
you can still provide the user with separate 'get count' button. We used
that technique in one of our apps because we had tables with hundreds of
millions of rows and querying was ad-hoc. Users could still navigate with
'next' and 'prev' button as well as jump to specific pages.
Note that SELECT COUNT(*) will use the narrowest useful index so index
tuning can help performance. Also, @.@.CURSOR_ROWS is an option if you are
using a cursor.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
news:uZjrlKHbGHA.4972@.TK2MSFTNGP03.phx.gbl...
> Thanks Dan for these numbers. In the mean time I performed some tests of
> my own. To my surprise, I found out that main problem is not with cursors
> and result sets, but with SELECT COUNT(*)!
> For 50k rows and when page is at the beginning of table COUNT takes about
> 10x more time than fetching 30 full rows!
> I need that count, to display exact number of pages to user.
> Since I'm making admin utility I also thought to show row count for each
> table in a database but I'm not sure about that now.
> Dejan|||I forgot to add that you can get an accurate table rowcount in SQL Server
2005 with:
SELECT rowcnt
FROM sysindexes
WHERE id = OBJECT_ID('dbo.MyTable)
AND indid IN(0,1)
The returned value is an approximation in SQL 2000 and might not be
accurate.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%23874LaHbGHA.3812@.TK2MSFTNGP04.phx.gbl...
> If a count of the number of rows is not needed for your paging technique,
> you can still provide the user with separate 'get count' button. We used
> that technique in one of our apps because we had tables with hundreds of
> millions of rows and querying was ad-hoc. Users could still navigate with
> 'next' and 'prev' button as well as jump to specific pages.
> Note that SELECT COUNT(*) will use the narrowest useful index so index
> tuning can help performance. Also, @.@.CURSOR_ROWS is an option if you are
> using a cursor.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Dejan Grujic" <dejan.grujic@.REMOVE.cogin.com.NO_SPAM> wrote in message
> news:uZjrlKHbGHA.4972@.TK2MSFTNGP03.phx.gbl...
>

Is there any way to blank out certain columns in a single sele

The users doesn't want the data for these 4 columns to print out unless the
formID is one of the ones selected. I can use "" instead. The dataset is
sent to Crystal Report to print out so it's better using "". I think Crysta
l
prints out the word "NULL" if they're set to NULL.
Thanks.
"Raymond D'Anjou" wrote:

> Add a Case for each of these columns.
> Example:
> ...CASE when b.formID in ('2', '16', '11', '12', '1', '13', '10') then NU
LL
> else b.form end as form,...
> "Alpha" <Alpha@.discussions.microsoft.com> wrote in message
> news:9BD3F043-072D-4598-B2BB-BA63529EA142@.microsoft.com...
>
>I don't know anything about Crystal report.
You can use '' for Text datatypes but the numerics and dates may not give
you what you want.
You may have to do a bit of CASTing for these types.
If the users can only choose 1 formID, just build your query differently in
C#.
if formid in ('2', '16', '11', '12', '1', '13', '10') then
select t.tid, t.tdate, t.trip_ticket,t.source,'' AS form, '' AS
formdate, '' AS printed,...
else
select t.tid,t.tdate,t.trip_ticket,t.source,b.form, b.formdate,
b.printed,
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:D27DCE8F-4B22-41C1-BDDF-A344467FCD63@.microsoft.com...
> The users doesn't want the data for these 4 columns to print out unless
> the
> formID is one of the ones selected. I can use "" instead. The dataset is
> sent to Crystal Report to print out so it's better using "". I think
> Crystal
> prints out the word "NULL" if they're set to NULL.
> Thanks.
> "Raymond D'Anjou" wrote:
>|||Hi,Thank you for the reply. I think the system got messed up here. The
"NULL" answer was address to Mesa's question.
My question for you is: Do I just append the "Case..." to the end of my
select
statement?
"Alpha" wrote:
> The users doesn't want the data for these 4 columns to print out unless th
e
> formID is one of the ones selected. I can use "" instead. The dataset is
> sent to Crystal Report to print out so it's better using "". I think Crys
tal
> prints out the word "NULL" if they're set to NULL.
> Thanks.
> "Raymond D'Anjou" wrote:
>|||No, the Case replaces the column name in your Select statement.
So, instead of:
select b.form,...
select CASE when b.formID in ('2', '16', '11', '12', '1', '13', '10') then
NULL else b.form end as form,
"Alpha" <Alpha@.discussions.microsoft.com> wrote in message
news:ED00B94C-E471-4508-9F1C-D8E2803E47EC@.microsoft.com...
> Hi,Thank you for the reply. I think the system got messed up here. The
> "NULL" answer was address to Mesa's question.
> My question for you is: Do I just append the "Case..." to the end of my
> select
> statement?
>
> "Alpha" wrote:
>

Is there another way out?

Guys,
We have a transactional replication set up with a single publisher and
a single subscriber.
Recently, replication failed due to the following reason:
Data, erroneously, was deleted from subscriber. Then, to make the
publisher and subscriber synch, the same data was deleted from
publisher.
At that point, the publisher, as it should, attempted to delete the
data in the subscriber. But since the data is not there, the attempt
failed, and replication failed.
I tried to restart the agent, but replication kept failing. Since
nothing worked, I just deleted the subscription and recreated it. The
replication then started, but since it had to go through all the steps
again (i.e. Snapshot ...), it took us 2 hours to get the data back in
synch.
Luckily this happened in the testing environment. If it would be
production, we would be in trouble, as availability is our #1 concern.
Would anyone know of any other (i.e. more elegant) way I could've
solved this problem?
The only other idea I had was the following: It is possible that the
data which the publisher plans to delete is temporarily stored in some
file and if that file is located and deleted, and then the agent is
restarted, it could possibly work - but, I could not locate any such
files - and this is a completely hypothetical idea.
Any suggestions would be appreciated.
Thanks
have a look at the continue on data consistency error profile.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"sql_er" <sql_er@.yahoo.com> wrote in message
news:1165954222.427226.20530@.j44g2000cwa.googlegro ups.com...
> Guys,
> We have a transactional replication set up with a single publisher and
> a single subscriber.
> Recently, replication failed due to the following reason:
> Data, erroneously, was deleted from subscriber. Then, to make the
> publisher and subscriber synch, the same data was deleted from
> publisher.
> At that point, the publisher, as it should, attempted to delete the
> data in the subscriber. But since the data is not there, the attempt
> failed, and replication failed.
> I tried to restart the agent, but replication kept failing. Since
> nothing worked, I just deleted the subscription and recreated it. The
> replication then started, but since it had to go through all the steps
> again (i.e. Snapshot ...), it took us 2 hours to get the data back in
> synch.
> Luckily this happened in the testing environment. If it would be
> production, we would be in trouble, as availability is our #1 concern.
> Would anyone know of any other (i.e. more elegant) way I could've
> solved this problem?
> The only other idea I had was the following: It is possible that the
> data which the publisher plans to delete is temporarily stored in some
> file and if that file is located and deleted, and then the agent is
> restarted, it could possibly work - but, I could not locate any such
> files - and this is a completely hypothetical idea.
>
> Any suggestions would be appreciated.
>
> Thanks
>