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 get isqlw running under sql 2005 install

I regulary would have 4 or 5 separate sessions of isqlw running.. and it is much less overhead than running multiple sql server managment sessions and also much better in the way the windows tile... the new interface sucks in my opinion ..

thanks in advance

ok to answer my own question

get the following files off of an old 2000 install and put them somewhere in your path

I put them in the winnt directory

1/30/2006 10:38a 352,828 isqlw.exe
1/30/2006 10:38a 401,984 sqlqry.dll
1/30/2006 10:38a 279,104 pfutil80.dll
1/30/2006 10:39a 414,272 pfclnt80.dll
1/30/2006 11:11a <DIR> ..

now put the following file in the directory as shown also from an existing 2000 install

Directory of E:\Program Files\Microsoft SQL Server\90\Tools\Binn\Resources\1033

10/14/2005 03:44a 16,088 PFCLNT90.rll
1 File(s) 16,088 bytes

(all should be ok as you are not replacing any files in 2005 these are all addition files pulled over from 2000)

now isqlw works on my machine...

if you want to get it working from external tools in the sql server managemtn window you also have to add the following rll's in

Directory of E:\WINNT\Resources\1033

01/30/2006 11:12a 28,672 pfclnt80.rll
01/30/2006 11:23a 147,456 pfutil80.rll
01/30/2006 11:23a 184,320 sqlqry.rll
01/30/2006 11:24a <DIR> ..
01/30/2006 11:24a <DIR> .
01/30/2006 11:24a 245,760 isqlw.rll
4 File(s) 606,208 bytes

still cannot figure out how to get it to pass the database and u/pw in so you don't have to enter those when launching isqlw from the sql server management studio there seems to be no option for thse parameters