I have a really weird query involving combining in a complex look. I analyzed the output from the view, built some indexes and earned the query within a second when starting from MSSQL Management Studio. However, when starting from Perl via ODBC, the same request takes about 80 seconds to return.
I dropped almost 8 hours on it, and it continues to bother me. During this time, I ran the query from Perl and copied it verbatim to Studio, I wrapped it in a stored procedure (which makes it execute about 2.5 minutes from the BOTH clients!), I looked for ODBC and MSSQL search caches, I watched how the request is launched through Activity Monitor (it spends most of its time in the general waiting state SLEEP_TASK) and Profiler (the select statement receives one line that does not appear before it is executed), and I started reading about performance bottlenecks.
I did not notice this problem with any other requests from Perl and, unfortunately, we do not have a database administrator on the site. I am a programmer who has done several database administrators, but it seems to me that I am in the dark with this. My best guess is that Studio has some kind of query cache that the ODBC client cannot receive, but restarting Studio does not make the request the first execution longer, so it does not look like it just because every new ODBC connection starts from an empty cache.
Without going into view definitions, the basic query is very simple:
SELECT * FROM VIEW1 LEFT OUTER JOIN VIEW2 WHERE SECTION = ? AND ID = ?
The delay disappears when I drop VIEW2, but I need data from this view. Three times I rewrote the view in an attempt to simplify and increase efficiency, but it looks like a dead end, since the request works fine from Studio. The query returns only one row, but even discards the identifier criteria and selecting all 56k rows for the entire section takes only 40 seconds from Studio. Any other ideas?
Edit 2/8: The related article by @Remus Rusanu was pretty clear, but I'm afraid this is not entirely applicable. Now it seems pretty obvious that this is not ODBC at all, but that when I hard-argument the arguments and parameterize them, I have different execution plans. I can reproduce this in SSMS:
SELECT * FROM VIEW1 LEFT OUTER JOIN VIEW2 WHERE SECTION = 'a' AND ID = 'b'
100 times faster than
DECLARE @p1 VARCHAR(8), @p2 VARCHAR(3) SET @p1 = 'a' SET @p2 = 'b' SELECT * FROM VIEW1 LEFT OUTER JOIN VIEW2 WHERE SECTION = @p1 AND ID = @p2
Unfortunately, I still do not understand why the first one should get an execution plan that takes two orders of magnitude less time than the parameterized version for any SECTION and ID values that I can use for this. This may be a flaw in SQL Server, but it seems silly that the inputs are known in both places, but one takes much longer. If the SQL server recalculates the parameterized plan from scratch every time, as it should be for the different constant values that I supply, it will be 100 times faster. None of the RECOMPILE options suggested by this article help.
I think @GSerg called it below. I have not yet proven that this does not happen with the window function used externally for presentation, but it describes the same thing and the time difference remains unclear.
If I don’t have enough time to work on this, I will try to adapt some article tips and enforce a permanent execution plan on a parameterized version, but there seems to be a lot of that there should be unnecessary problems.