c# - Unusual SQL/Data issues -

c# - Unusual SQL/Data issues -

we have study has been giving serious issues, decided set console application in order troubleshoot issues.

the study simple single select sql, returning approximately 25 columns, , our date range can 3-6 months, returning around 10k rows, not talking lot of data.

here whats happening, when study runs, timing out our website, in console, takes anywhere 13-18 mins finish, wait seems happen @ da.fill(ds);

now here unusual thing, runs approximately 1-3 seconds within sql server management studio, , when our delphi developers create similar application, few seconds run, happens using .net

we tried changing dataset loading datareader, using code.. using (var dr = _command.executereader()) { if (dr.hasrows) { int = 0; while (dr.read()) { var startread = datetime.now; console.write("{2}\t{0}\t{1}\t", dr.getint32(0), dr.getstring(1), i); var tookread = datetime.now.subtract(startread); console.writeline("took: " + tookread); i++; } } did not help @ all, displays in chucks has frequent delays. i'm thinking sql, can't explain why works fine in delphi , in sql management studio.

i've tried using .net 2.0, 3.5 , 4, happens on frameworks.

here code

public static dataset getdata() { var = datetime.now; var _command = new sqlcommand(); var _connection = new sqlconnection(); seek { _connection.connectionstring = connectionstring; _command.connection = _connection; _command.commandtext = storedprocedure; _command.commandtype = commandtype.storedprocedure; _command.commandtimeout = 60; if (string.isnullorempty(_connection.connectionstring)) { throw new exception("connection string not supplied"); } _command.parameters.add(new sqlparameter("datefrom", datefrom)); _command.parameters.add(new sqlparameter("dateto", dateto)); sqldataadapter da; var ds = new dataset(); _connection.open(); var done = datetime.now; da = new sqldataadapter(_command); da.fill(ds); if (ds == null) { throw new exception("dataset null."); } if (ds.tables.count == 0) { throw new exception("table count 0"); } var took = done.subtract(now); homecoming ds; } grab (exception ex) { file.writealltext(path.combine(application.startuppath, string.format("exception{0:mmddyyyy_hhmmss}.log", datetime.now)), ex.tostring()); } { if (_connection.state != connectionstate.closed) { _connection.close(); } } homecoming null; }

any ideas? our dba blaming framework, i'm blaming in sql.. (maybe statistics, or corrupted db)

differences in sql performance between .net , other clients (sql management studio) downwards connections beingness configured differently - frequent culprits ansi_nulls; ansi_padding.

try looking @ how connection configured in sql management studio, replicate same thing in .net application.

c# asp.net sql stored-procedures