8

I have two DataBases (DB1 & DB2 : both DBs are same, DB2 is created from the backup of DB1). When I run a stored procedure SP1 on both DBs it takes approximately 2 seconds to give me an output (select statements) on both DBs.

Now the problem is when I point these DBs from a service and try to use DataAdapter.Fill method, it gives me different time(54 - 63 seconds on DB1 and 42 - 44 seconds on DB2) on both DBs consistently. Noted that I'm using same service to point DBs so it couldn't be service behave/performance. Now my question is:

What could be the reason for this? Any suggestions are welcome that What should I look into?

Helping Info:

  1. Both DB are on different servers(identical configuration) but since executing the SP on SQL Server Management Studio take the same time on both DBs so I ruled out the possibility of DB server performance. Network delay could be a factor But higlly unlikely as both servers are on same network and infact on same physical location. This is my last option to check.

  2. Some other services are using SQLDependency ON DB1. Which consistently fill DataAdapter(s), could this be the reason for my DataAdapter fill method to slow down? (less likely as I'm guessing)

As requested in comments below is code that is filling the DataSet:

DataSet Fill

PS: The time mentioned above is the execution time of the code line highlighted in the above image.

Luis Gouveia
  • 8,334
  • 9
  • 46
  • 68
Kylo Ren
  • 8,551
  • 6
  • 41
  • 66
  • If `DB2` is a restored backup of `DB1`, then could it be that `DB1` simply has more data returned by the procedure? I'm thinking that `DB1` could be a production database while `DB2` is a test database. So the first one would likely be filled with new data at a faster pace... – user1429080 May 20 '16 at 06:06
  • @user1429080 no, data is same both returned by SP and overall in DB, there is no production DB scenario, both are Dev. DB on my local environment – Kylo Ren May 20 '16 at 06:23
  • 1) Use the SQL profiler to check all queries that run in DB1 and DB2 when you execute the Fill method. Is there any difference? Check the sql load on both servers when you execute the Fill. 2) You said that it is only takes 2 secs when you execute the query in the SQL studio. it should be the approximately the same when you do Fill (if it is the same query) but it takes 40x times slowly. Do you have some logic attached to your objects? – Sergey L May 20 '16 at 09:49
  • @SergeyL I've already run profiler . the no of queries in a timespan would be different for two DBs as there are morr users on DB1 compare to DB2. But the problem is when query is run of Management studio it takes same time of both DBs, but when the same application(.net) points to those db, output time is different. I hope you understand the problem now. – Kylo Ren May 20 '16 at 10:46
  • 1
    Does your `select` query contain `where` with parameters? – Ivan Stoev May 20 '16 at 11:21
  • @IvanStoev yes. How does that fact matter? – Kylo Ren May 20 '16 at 12:40
  • 4
    Usually when you hear fast inside SSMS, slow outside, it smells like parameter sniffing issue. http://stackoverflow.com/search?q=parameter+sniffing – Ivan Stoev May 20 '16 at 12:58
  • @IvanStoev does this also cause to slow perform in one DB and relatively fast in another? – Kylo Ren May 21 '16 at 05:24
  • A must read: [Slow in the Application, Fast in SSMS? Understanding Performance Mysteries](http://www.sommarskog.se/query-plan-mysteries.html) by Erland Sommarskog. – Vladimir Baranov May 21 '16 at 09:22
  • clear cache from db server . calculate exact time diff ie u just put select SYSDATETIME() ; exec yourproc; select SYSDATETIME(); if there is a time diff ... – sandeep rawat May 22 '16 at 05:46
  • @VladimirBaranov thanks, i'll have a look – Kylo Ren May 23 '16 at 12:10
  • @sandeeprawat I've calculated the time difference it consistently different. – Kylo Ren May 23 '16 at 12:11
  • 1. How are you running the procedure which results in a 2 second run time? What tool are you using? Are you remoting into the machines to do this? 2. Can you show us the query you're executing? 3. Can you show us the database diagram? 4. Can you run your application on the same machine as the database? What happens? 5. Can you restore the database locally, and reproduce the issue? 6. Where are you, and where are the databases located? 7. Can you show us your code which is loading the rows? 8. How is your `DataSet`/`DataTable` setup in code? Does it have any constraints attached to it? – Rob May 24 '16 at 05:29
  • @Rob 1. I'm executing the SP in SSMS, 2. I can't show you the query right now, will update as last option but it's just a select query from some tables. 3. DB diagram I'll update in the question. 4. No I can't run the application on same machine. 5. No, I can't. 6. my machine and DBs are on same network. 7. the code is most basic code of DataAdapter class of ADO.Net filling a DataSet, 8. The DataSet doesn't have any constrains on it, it's just declared at DataSet ds=new DataSet(). – Kylo Ren May 24 '16 at 05:52
  • @Rob I think I need to a bit clear what my problem is, I'm not at all concerned about why query is giving the output in 2 seconds in SSMS and in 50 seconds in .net code. My concern is when the same application is pointed to two identical DB's why is it giving different performance, now all the factors I thought were relevant I've mentioned in the question. please let me know if i need to provide any more . – Kylo Ren May 24 '16 at 05:56
  • @KyloRen In any case, the only answer you will be able to get is "it's probably latency", because you're unable to give us anything more to go on. In fact, since it's such a large difference with no obvious cause, it's likely you will need to show us all the details so that someone who is familiar with the edge cases of Sql Server can possibly point out some issues. Saying your code is basic doesn't help at all. Basic code can be flawed as well – Rob May 24 '16 at 05:59
  • @Rob As I said the both DB's are on same network & same physical locations. And as they are giving the same performance in SSMS, so latency doesn't seems a probable reason here. and let's say the .net code is flawed how can it make the difference.( I'm not being sarcastic here, I meant have you ever encountered such situation? any example cases? I'm not saying such code couldn't exist but In my code I'm failed to see any possibility of such kind. nonetheless i'll update the code in question) – Kylo Ren May 24 '16 at 06:06
  • A couple of interesting points; If it is consistent times you see from the code - eg. always the 10 seconds slower - but you get the exact same times from SSSM, it would indicate it's not the servers, so perhaps Parameter sniffing. If not check servers. - Check Power settings (performance > balanced). - Is disks the same? (not one SAN and the other SSD). Are they virtual servers? - Are load the same? - Are services running on the servers the same. Etc. Personally, I would make an application that just adds numbers and run to compare server performance on CPU level. – Allan S. Hansen May 24 '16 at 07:46
  • @AllanS.Hansen thanks. I'll look again based on your points – Kylo Ren May 24 '16 at 08:59
  • Try separating the query execution from materialization (which are encapsulated in `Fill` method). For instance, add this before `Fill` and see what times you've got: `var executeTimer = Stopwatch.StartNew(); var reader = cmdCommandObject.ExecuteReader(); executeTimer.Stop(); var readTimer = Stopwatch.StartNew(); while (reader.Read()) { } readTimer.Stop(); reader.Close();` – Ivan Stoev May 24 '16 at 12:39
  • Restoring a database to another server from a backup doesn't automatically mean that two databases are identical. See: http://dba.stackexchange.com/q/61754/57105 http://dba.stackexchange.com/q/53726/57105 http://stackoverflow.com/q/10677143/4116017 – Vladimir Baranov May 25 '16 at 00:56
  • @VladimirBaranov I'm not concerned about that... both DBs have same performance in SSMS, why different in ADO? – Kylo Ren May 25 '16 at 05:49
  • @IvanStoev can you be a bit clear what will this code achieve? – Kylo Ren May 25 '16 at 05:51
  • 2
    @KyloRen It's just for testing purpose, to help identifying if the problem is in db query execution (the first part - `ExecuteReader`) or materialization. If the most of the time is in the first part, then you should look at query plans, parameter sniffing etc. – Ivan Stoev May 25 '16 at 05:57
  • @IvanStoev ok, will test with this code – Kylo Ren May 25 '16 at 06:01
  • 1
    What happens when you 1. exchange the two servers? run DB1 from server2 and DB2 from server1 2. shrink databases 3. drop/recreate all indexes additional question: is there any difference between the query texts, plans and times (cpu read, duration, etc) when you check it from SQL Profiler? – Dexion May 26 '16 at 09:40
  • Have you tried these solutions? http://stackoverflow.com/questions/250713/sqldataadapter-fill-method-slow – devlin carnate May 26 '16 at 17:53
  • `SqlDataAdapter` has much more methods then you use. `Fill` method uses `DataReader` object which can give better performance. Try `dstTemp/Load(cmdCommandObject.ExecuteReader())` instead. – Alex Kudryashev May 27 '16 at 01:31

3 Answers3

1

That sounds very much like a query plan issue.

Erland Sommerskog has written an excellent article about this kind of problems, Slow in the Application, Fast in SSMS?.

My first guess would be "The Default Settings", but it might be one of the other issues, too.

TToni
  • 9,145
  • 1
  • 28
  • 42
  • this is not my problem exactly. My problem was the performance discrepancy on two DBs . – Kylo Ren Jun 08 '16 at 16:42
  • @KyloRen: Wait, so you worry about the 20% difference between DB1 and DB2, and not about the 2000% difference between SSMS and ADO.NET? – TToni Jun 09 '16 at 07:11
  • Yes, it's was massive data set so a dataset is taking time to fill, it'a the different performance on both DB that i can't figure out why? – Kylo Ren Jun 09 '16 at 09:23
  • @KyloRen First you should check for disk activity during the operation. If the data needs to be read from disk, it could be for example that the restored DB is less fragmented on the file system and so reads are faster. If there is no activity on the disk, it comes down to different query plans again. The mentioned article has some good info on that, too. – TToni Jun 09 '16 at 09:43
  • fragmentation is a good suggestion to look for. I didn't checked for it cause on SSMS both db's were giving same performance? was i right to do so or fragmentation can behave such way? – Kylo Ren Jun 09 '16 at 09:47
  • A response that takes 2 sec versus a response that takes over 40 sec obviously does very different things. A 2 sec response is unlikely to read from disk, especially when done repeatedly. If you run both DB on the same SQL Server instance they may also use different amounts of memory, they occupy different sectors on the hard drive. If you have local drives, they have different performance depending on where the information is, how fragmented it is etc.. So basically you want to monitor what goes on on your system and from there narrow down to what goes on in SQL Server. – TToni Jun 09 '16 at 10:40
0

Have you tried not using the SQL.StoredProcedure and just run it as a line of SQL: "exec dbname.dbo.storedprocname params".

Its a bit more work because you'll have to loop around the parameters to add to the string at the end but its a SQL string, it doesn't care what you are doing, its not doing anything funny behind the scenes. Should have similar times, if this is failing, try checking things like indexes etc.. on db tables that the Stored Procedure is using.

Ian Martin
  • 11
  • 3
0

Step one - rebuild or reorg your indexes. This is usually the most common performance issue with SQL Server and is easy to fix. Restart SQL Server some times this also a matters

Khadar
  • 79
  • 6