| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2007-01-02 : 11:20:32
|
| I have an SP, that if I execute through QA, uses a very efficient query plan, and returns data in less than 1 second. If I call the same SP through .net, it uses a much different query plan which results in a hundred thousand reads and a timeout for the front end.I haev set the proc to execute with recompile, this did not help. I freed the proc cache, and this helped for the next execution of the proc call, but not subsequent ones. It seems like .net is forcing SQL to use its own, really @#%$^ version of the query plan, and I want it to knock this off.Any suggestions? |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-01-02 : 11:41:57
|
| You could force the query to use a particular (good/helpful) index which might bump the SQL Engine to pick a useful plan regardless of the execution method....sometimes it "needs" hand-tuning! |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-02 : 12:07:20
|
quote:
Originally posted by AndrewMurphy
You could force the query to use a particular (good/helpful) index which might bump the SQL Engine to pick a useful plan regardless of the execution method....sometimes it "needs" hand-tuning!
That's what we had to do as well for this type of issue. We encountered the problem when the index that should be used was a composite index. The friggin' optimizer kept wanting to use the PK index instead when called from our application. So we forced it to use the unique composite index via an index hint. I believe sql-server-performance.com has an article about this very problem.Tara Kizer |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-02 : 13:19:22
|
| Unfortunately, that won't help. Everything is already using an index seek on the best index on the table. When running through the .net client, it's not applying some of the join conditions, so it's resulting in 17,000 index seeks on the join instead of 1. Then it filters them. This never happens when running through QA, only through .netThanks for the suggestions, though.. Hopefully I'll get a few more! |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-02 : 13:32:45
|
| But is it using the correct indexes on each of the tables?Tara Kizer |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-02 : 13:46:10
|
| Yes.The difference is that if I execute it in QA, it uses all three parts of my composite index. If I execute it through .net, it uses only the first two. The statement for the sproc looks like this:[CODE] JOIN cssprod.dbo.def_frd frd ON lnk.doc_frm_nbr = frd.doc_frm_nbr AND lnk.doc_frm_nbr IN ('web', 'prt', 'arc', 'cnf', 'src') AND frd.sys_cod = 'rep' AND frd.dev_ty = 'pfe' AND frd.ts = 0 WHERE lnk.dev_ty = 'pfe'[/CODE]and when I use QA, the plan indicates that the lnk table is constrained to dev_ty = 'pfe' and the doc_frm_nbr values listed. If I use .net, though, it leaves out the doc_frm_nbr values in its initial index seek, and thus returns 17,000 rows of useless junk that doesn't match when joined to the frd table, and 1 row which does. If I free the proc cache, it will work OK for a few executes, but then it goes right back to doing the wrong thing again. |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-02 : 13:49:12
|
| Here is the relevant part of the query plan when I execute it through .NET:[CODE]|--Index Seek(OBJECT:([cssimagecopy].[dbo].[bob_lnk].[bob_lnk_alt2]), SEEK:([bob_lnk].[dev_ty]='pfe' AND [bob_lnk].[dev_key_id]=Convert([@entity_id])) ORDERED FORWARD)[/CODE] and here is the part of the plan when using QA:[CODE]|--Index Seek(OBJECT:([cssimagecopy].[dbo].[bob_lnk].[bob_lnk_alt2]), SEEK:([bob_lnk].[dev_ty]='pfe' AND [bob_lnk].[dev_key_id]=Convert([@entity_id]) AND [bob_lnk].[doc_frm_nbr]='arc' OR [bob_lnk].[dev_ty]='pfe' AND [bob_lnk].[dev_key_id]=Convert([@entity_id]) AND [bob_lnk].[doc_frm_nbr]='cnf' OR [bob_lnk].[dev_ty]='pfe' AND [bob_lnk].[dev_key_id]=Convert([@entity_id]) AND [bob_lnk].[doc_frm_nbr]='prt' OR [bob_lnk].[dev_ty]='pfe' AND [bob_lnk].[dev_key_id]=Convert([@entity_id]) AND [bob_lnk].[doc_frm_nbr]='src' OR [bob_lnk].[dev_ty]='pfe' AND [bob_lnk].[dev_key_id]=Convert([@entity_id]) AND [bob_lnk].[doc_frm_nbr]='web') ORDERED FORWARD)[/CODE]Should I post this to the .NET section of this forum to see if one our resident developer experts knows why this might be happening? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-02 : 13:59:49
|
| try updating statistics. probably bad query plan cached. remember that execution plan is cached for 1st set of parameters the optimizer sees. update statistics will (1) update statistice :) and (2) force recompile of SP. |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-02 : 14:20:49
|
| The indexes have been completely rebuilt recently (which updates the stats), and the proc has been recompiled multiple times. It seems to be defaulting to a bad query plan only for .net, and not for QA. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-02 : 14:23:11
|
| When you see the query plan in Query Analyzer, are you looking at the actual execution plan or the estimated query plan? Or maybe you are viewing them in SQL Profiler, which would mean actual...Tara Kizer |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-02 : 14:34:16
|
| Yep, I am taking them from profiler, so it's the real deal :) |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-02 : 15:16:15
|
| Also, it is using the sqlhelper.runprocedure method, I don't know if this will make any difference. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2007-01-02 : 16:00:42
|
| are u caching query in .Net app? may want to turn that off if so. |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-02 : 16:45:21
|
| How would I know if I am doing this? I need to know what to tell the developer in order to turn it off. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
|
|
AndrewMurphy
Master Smack Fu Yak Hacker
2916 Posts |
Posted - 2007-01-03 : 04:35:28
|
| Are you being hit by "parameter sniffing"?...search here for solutions for same |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-03 : 05:31:45
|
| Am I being thick here?How can SQL use a different query plan for an identical query (it is identical, right?) depending on the source application?Kristen |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-03 : 11:18:24
|
| You're not thick, you're scratching your head and wondering the same thing I was wondering....until this morning, when I discovered that there were two procs with the same name (but not the same ownership) in the DB, and the application was pointing to the one that I was not executing, but was being executed through the application.So, I missed the forest for the trees. Thanks for everyone's help! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-03 : 13:49:49
|
| BTDTGTTS!! |
|
|
|
MuadDBA
628 Posts |
Posted - 2007-01-03 : 15:44:44
|
| Apparently I forgot to pay my membership dues to the Acronym club... |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-03 : 16:19:42
|
http://www.google.co.uk/search?q=BTDTGTTS+Acronym  |
|
|
|
Next Page
|
.net SP call uses bad query plan....