SqlDataReader SLOW

[LR]

I am putting data into Excel from a SQL 2000 DB (Not MSDE) via a
SqlDataReader
(ADO.NET/VB.NET) My dr calls a stored procedure on the SQL server.
When I run this sproc in query analyser, it returns the results within 2
seconds.
When using the sqldatareader, the same sproc takes 30 to 40 seconds to run
from the same PC
that was running QA. Any suggestions?
Relevant code is below:


' Excel code here

Dim SqlConn1 As SqlConnection = New SqlConnection("workstation id=MyWS;user
id=xxxx;pwd=xxxx;data sourc" & _

"e=""MySQLServer"";persist security info=False;initial catalog=MyDB")


'

Dim TestConn As SqlCommand = New SqlCommand("dbo.MySproc")

TestConn.CommandType = CommandType.StoredProcedure

TestConn.Connection = SqlConn1

TestConn.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue,
False, CType(0, Byte), CType(0, Byte), "",
System.Data.DataRowVersion.Current, Nothing))

TestConn.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DT_START",
System.Data.SqlDbType.DateTime, 8)).Value = dtstart2.Value.Date

TestConn.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DT_STOP",
System.Data.SqlDbType.DateTime, 8)).Value = dtend2.Value.Date

Dim Reader As SqlClient.SqlDataReader

SqlConn1.Open()

Reader = TestConn.ExecuteReader()

Dim i, j As Integer

i = 3

While Reader.Read

i = i + 1

For j = 0 To 14

excelWorksheet.Cells(i, j + 1) = Reader(j).ToString()

Next j

End While

Reader.Close()

SqlConn1.Close()

' more excel code here

 

[Miha Markic]

Hi LR,

Are you retrieving a lot of rows?
Does your time include communication with Excel.

Try comparing only ExecuteReader method with Query Analyzer time.
Also, some threads below there was menitioned that ARITHABORT might
interfere with query plan.

 

[LR]

Thanks for the reply...
The rows depend on the daterange from the datetimepickers on the form.
(my parameters = dtpicker.value.date)
A 1 month query (the one that would take 80 to 90 secs) will yield about
1-2k records, and as you can see from my code
there are 15 columns. The largest data would come from the column that has
anywhere from 1 to 20 VARCHARs
(not a big deal, and it's not in the where clause of my sproc) This is not a
lot of data.
The timer is setup right before the SqlConn1.Open()
and stops right after
Reader.Close()
SqlConn1.Close()
I am not too concerned with including connection time in the mix because it
takes less than a second.
My time has to include communication with Excel (I am assuming) because I
must have the workbook open in order
to fill it from the dr as it recieves the data. I may be wrong (newbie to
coding) but as the reader gets a record, VB puts it into Excel, then the
next record to the next row in Excel. Is there a cache or buffer that I can
increase? (DataAdapter/Ds was even slower) If I am wrong with any of this
please let me know, such as does the datareader actually grab all the data
first and then process it into Excel like a DataSet?
I will move the timer after the conn.open() just in case, but query analyser
gets all results in 2 seconds.
The data adapter/ dataset took even longer to fill, not including Excel
time. I will look into the ARITHABORT also, but I can't seem to find it when
searching this newsgroup. Would it be advantagious to seperate all of this
into different subs?
I have the dr and the Excel open/formatting all under button2_click.
Thanks again for your reply.

 

[William Ryan]

If you call executeReader and look at while dr.Read(), does it take that
long to get to the end? I've done a good amount of Excel Interop and it's
really slow.

You may want to call some code from within Excel using a QueryTable (if you
record a macro with Data->GetExternalData and follow the wizard, you'll see
how it workds) - this is the fastest way I've seen to populate an Excel
DataSheet and you still have full control over the object model. Typically,
stuff that took 45-60 seconds to do using a DataTable or Reader and
iteratively adding the cells took about 5-8 seconds.

If you aren't familiar with QueryTable, let me knwo and I can send you some
code with it - it really can work wonders for you.

HTH,

Bill

 

[LR]

hehe feel like such an idiot... why didn't I think of that?
I have been using the macro and going into office vb editor to look at the
module to get code on how to change format of cell, etc...

Will it be required to set visible = false while this is running from my
program, or can I leave Excel visible without getting an exception?
I think using the reader and also the dataadapter dataset I had to hide the
app until the spreadsheet was done.

Hey thanks for the input. I wonder what it is that makes excel iterop so
slow?

 

[scorpion53061]

Hi LR,

This may be of some use to you.

http://www.kjmsolutions.com/datasetarray.htm

 

[scorpion53061]

Excel code if you are writing cell by cell you see can be very very slow.
That might be why are thinking it is taking so long.