August 12, 2010 at 10:33 am
If you don't want SQLServer to cache query plan add "OPTION(RECOMPILE)" to the end of your query!
oh yes
exec sp_executesql N'select getdate() option (recompile)'
that works nicely 🙂
but not this:
exec sp_executesql N'select getdate()' with recompile
August 12, 2010 at 10:43 am
I hope you understand the differnece between your two examples.
The first one tells to SQLServer to recompile query which is executed by sp_executesql. You can use this option for some queries even inside of stored proc or in your query window.
The second one, causes SQLServer to recompile procedure called (in your case it is sp_executesql). You can use this option in procedure declaration section, in this case the proc will be recompiled every time it's called.
August 12, 2010 at 12:00 pm
I'm not going to provide a primer on how to accomplish sql injection, but I can tell you the code you've provided is not sufficient to prevent it. I don't think many victims of injection are thinking their methods are leaving them vulnerable.
Very few web apps can function with read only access. However, if it is the case that yours can, injection is definitely less of a threat but there are still issues since they can see your database structure and potentially find other issues. On the other hand, using industry proven standards, attackers have absolutely zero access to anything beside the specific functionality provided by the specific stored procedures that the account has permission to execute. (This also contributes to helping prevent other security issues beyond injection.)
Best practices aren't meant to say you should never do something else, but you should have very good reasons to do so, especially in a case like this when it's completely undisputed what the most secure method is. The idea that there is some horrible cost associated with proc caching would not fall under the category of a good reason.
August 12, 2010 at 1:28 pm
Also, it's risky to base your security practices on read-only access.
What happens a year from now when the app needs changed to allow UPDATEs / DELETEs? Your security approach will cause enormous problems, because it was based on a temporary limitation rather a more well-rounded consideration.
Scott Pletcher, SQL Server MVP 2008-2010
August 15, 2010 at 12:38 pm
I wanted to let everyone who helped me know what I ended up doing.
1st I get all the location ID's on a specific route (usually about 20 rows).
SELECT tblLocation.ID
FROM tblLocation
WHERE tbllocation.Inactive = '0'
AND (tblLocation.Route LIKE '201-%')
This executes and returns the rows in less than a second.
Then I get a second list of manifest location ID's on the same route.
SELECT DISTINCT LocationID
FROM tblManifest
LEFT JOIN tblLocation ON tblLocation.ID = tblManifest.LocationID
LEFT OUTER JOIN tblManifestDetail ON tblManifestDetail.ManifestID = tblManifest.ID
WHERE (SELECT DISTINCT MAX(t1.ManifestDate) FROM tblManifest t1 WHERE t1.ID = tblManifestDetail.ManifestID) >= '08/15/2008'
AND (tblManifestDetail.MatCode IN ('1AGA', '3AGA', '3EGA'))
AND (tblLocation.Route LIKE '201-%')
This executes a little slower (0.5 to 4 seconds) and will have zero to a few more rows than the 1st table.
Then I loop through the 1st table rows and look for 2nd table rows that are not in the 1st table.
for (int i = 0; i < tempLoc.Rows.Count; ++i) {
string locID = Convert.ToString(tempLoc.Rows[0]);
int x = manifestBindingSource.Find("LocationID", locID);
if (x == -1)
{
othernewcustomers += "OR tblLocation.ID = '" + locID + "' ";
}
}
I know this is not eligante at all. But it executes much faster than using "NOT IN."
I tested it with all 40+ routes and the slowest was 4.6 seconds. 98% execute in less than 2 seconds.
Using "NOT IN" was taking from 2 to 18 seconds, which is not acceptable.
I tried "NOT IN" in a stored procedure and there was no improvement.
You may notice in my previous posts that "AND (tblLocation.Route LIKE '201-%')" is not in the "NOT IN" selection.
I tried using it in the "NOT IN" selection and it slowed things down even more.
I am hindered by using SQL 2005 at home where I work most of the time, but at the office they are using SQL 2000.
(In a month we will switch to SQL 2005, and after we can get rid of all the old code we will switch again to the lastest SQL version).
I found solutions that ran somewhat faster at home but were very slow at the office and the other way around.
This solution runs equally fast on both SQL 2000 and 2005.
It was suggested to me that I should use a professional. I have a professional help me from time to time (he frequents this forum).
I now know I should have gone to him first. But I was too anxious to get this running (he lives on the other side of the planet in India, and he's sleeping while I'm working).
I'm very aware that I will never get as good at this as most of you because I rarely need to do anything complicated.
At first it seemed to me this should be fairly easy to do. But it has taken me a lot of (unpaid) time.
Thank you everyone for helping me.
Dennis
August 16, 2010 at 2:39 am
Hi Dennis, thanks for posting the feedback.
I think there's room for performance improvement here. The second query can be rewritten slightly like this - reasons are in the code:
SELECT DISTINCT mh.LocationID
FROM tblManifest mh
-- the reference to l.Route in the WHERE clause turns the LEFT OUTER JOIN into an INNER JOIN
INNER JOIN tblLocation l ON l.ID = mh.LocationID
-- the reference to md.MatCode in the WHERE clause turns this into an INNER JOIN too
INNER JOIN tblManifestDetail md ON md.ManifestID = mh.ID
INNER JOIN (
SELECT ID, MAX_ManifestDate = MAX(ManifestDate)
FROM tblManifest
GROUP BY ID
HAVING MAX(ManifestDate) >= '08/15/2008'
) m ON m.ID = md.ManifestID
WHERE md.MatCode IN ('1AGA', '3AGA', '3EGA')
AND l.Route LIKE '201-%'
This should bring back the same result set as your original query no. 2
The above query is functionally equivalent to this SELECT DISTINCT l.LocationID
FROM tblLocation l
INNER JOIN tblManifest mh ON mh.LocationID = l.ID
-- the reference to md.MatCode in the WHERE clause turns this into an INNER JOIN too
INNER JOIN tblManifestDetail md ON md.ManifestID = mh.ID AND md.MatCode IN ('1AGA', '3AGA', '3EGA')
INNER JOIN (
SELECT ID, MAX_ManifestDate = MAX(ManifestDate)
FROM tblManifest
GROUP BY ID
HAVING MAX(ManifestDate) >= '08/15/2008'
) m ON m.ID = md.ManifestID
WHERE l.[Route] LIKE '201-%'
where the important change is the "driving table", from tblManifest to tblLocation.
Now, because your second query contains the whole set (and maybe more) of your first query, the first query can be left-joined to the second:
SELECT DISTINCT l1.ID
FROM tblLocation l1
LEFT JOIN tblLocation l2 ON l2.ID = l1.ID
AND l2.[Route] LIKE '201-%' AND l2.Inactive = '0'
INNER JOIN tblManifest mh ON mh.LocationID = l1.ID
-- the reference to md.MatCode in the WHERE clause turns this into an INNER JOIN too
INNER JOIN tblManifestDetail md ON md.ManifestID = mh.ID AND md.MatCode IN ('1AGA', '3AGA', '3EGA')
INNER JOIN (
SELECT ID, MAX_ManifestDate = MAX(ManifestDate)
FROM tblManifest
GROUP BY ID
HAVING MAX(ManifestDate) >= '08/15/2008'
) m ON m.ID = md.ManifestID
WHERE l1.[Route] LIKE '201-%'
AND l2.ID IS NULL
This new query looks for rows in the second query which don't exist in the first query. Give it a try.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2010 at 3:36 am
Thank you.
Trying the last example I get these:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'LocationID'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'LocationID'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'LocationID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LocationID'.
I tried to correct it but my mind is not use the syntax.
The first example does run faster.
August 16, 2010 at 3:41 am
dennisv (8/16/2010)
Thank you.Trying the last example I get these:
Msg 207, Level 16, State 1, Line 4
Invalid column name 'LocationID'.
Msg 207, Level 16, State 1, Line 4
Invalid column name 'LocationID'.
Msg 207, Level 16, State 1, Line 21
Invalid column name 'LocationID'.
Msg 207, Level 16, State 1, Line 1
Invalid column name 'LocationID'.
I tried to correct it but my mind is not use the syntax.
The first example does run faster.
Oops that's because tblLocation has ID as key, not LocationID. I've edited the query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
August 16, 2010 at 5:33 am
It now executes ok but is returning the wrong row(s).
August 16, 2010 at 5:57 am
Ok, starting at the beginning, does this query return the same as your second query:
SELECT DISTINCT mh.LocationID
FROM tblManifest mh
-- the reference to l.Route in the WHERE clause turns the LEFT OUTER JOIN into an INNER JOIN
INNER JOIN tblLocation l ON l.ID = mh.LocationID
-- the reference to md.MatCode in the WHERE clause turns this into an INNER JOIN too
INNER JOIN tblManifestDetail md ON md.ManifestID = mh.ID
INNER JOIN (
SELECT ID, MAX_ManifestDate = MAX(ManifestDate)
FROM tblManifest
GROUP BY ID
HAVING MAX(ManifestDate) >= '08/15/2008'
) m ON m.ID = md.ManifestID
WHERE md.MatCode IN ('1AGA', '3AGA', '3EGA')
AND l.Route LIKE '201-%'
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 10 posts - 31 through 39 (of 39 total)
You must be logged in to reply to this topic. Login to reply