Re: SqlDataReader performance


Try this instead:

dr = cmd.ExecuteReader();

string keyvalue = "";
int ordinal = dr.GetOrdinal(keyname);

while(dr.Read())
{
keyvalue = dr[ordinal].ToString();
...
}

Try also

keyvalue = (string)dr[ordinal]; // might not work

and

keyvalue = Convert.ToString(dr[ordinal]); // might be slower but don't know


without transaction (not necessary for read operations in most cases) and in
RELEASE mode without debugger attached (run without debugging in RELEASE
mode).

I'm not sure how the optimizer will treat your string keyvalue =
dr[keyname].ToString(); but many times I find it helps to move the
declaration of a string outside the loop and its also a good practice to
know the index of your field in advance so you don't have to locate it each
iteration... so its best to get the ordinal(s) before the loop begins. That
helps a bit to.

Beyond that, I'm not sure there's a whole lot else can be done to improve
performance source code wise based on the snippet provided.


Thanks,
Shawn

I've got a rater complex sql query that returns 30+ collums from 4 joined
tables.
The query performes acceptable in the Query analyzer.

dr = cmd.ExecuteReader();
//I retriev my data within a wile loop containing 3 primary sections
wile(dr.Rea())
{
1)
string keyvalue = dr[keyname].ToString()
2)
//Compare with previous key and ignore duplicates
3)
//Collect remainding collum data

}

On large resultsets 50k+ rows I see a terrible performance penalty on the
first point. 95% of the time used in completing this procedure is spendt
in
point 1).

Typical trace data:
Execute query 1.6 seconds
Collecting keys 1) 8.1 seconds
Comparing 2) 0 seconds
Collecting remaining data 3) 0.7 seconds.

As you can see most of the time is spendt getting data from the DataReader
the first time each row is accessed.

To be able to optimize this I need to understand the correlation between
the
SQL statement, the cmd.ExecuteReader() and the retrieval of data from the
DataReader.

Even on small resultsets the time spendt on 1) is unacceptable.

What affects the performance of the SqlDataReader?
What effect has a transaction on this?

Any input would be greatly appreciated!!

Jan

--
Do or die..


.

Relevant Pages

  • Re: Transactions - Infinite Loops
    ... This still doesn't sound like an infinite loop. ... > It was an unusual SQL Server response that we were unable to duplicate. ... > transaction if the wrong number of felds are being updated (i.e., ... > the query ran okay. ...
    (microsoft.public.sqlserver.programming)
  • Re: Transactions - Infinite Loops
    ... It was an unusual SQL Server response that we were unable to duplicate. ... the end of the transaction, but the query seemed to work fine - records were ... the query ran okay. ... Now I am thinking that perhaps the transaciton log loop was cuased by some ...
    (microsoft.public.sqlserver.programming)
  • Re: SQL Query
    ... If you use the Group By clause, ... As far as this query, you need three parts to your wehre AFAIK ... > This way I have to loop through all the dates. ... >>> example it can have hundreds of transaction added just in a month. ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Combo box bound field change
    ... Modify the row source to include both fields. ... Make the bound column the company name column. ... now you can change the company name any time and your query ... Make a copy of your transaction table, but copy only the structure, not the ...
    (microsoft.public.access.forms)
  • Re: Transaction Isolation Level
    ... This means that while I initiate a transaction selecting all new orders it ... My query is executed on a SQL server 2005. ... the isolation level you should use is snapshot isolation. ...
    (comp.databases.ms-sqlserver)
Loading