Of databases and the sacrifice of live chickens
-
I have a fairly simple stored proc in one of my databases that does a couple of simple inner joins and returns a result set. The proc accepts a user id and returns a set of gl accounts to which that user can apply invoices and such. When I pass in an admin user id, all the records come back instantly, no problem. When I use a user id with less elevated permissions, the query takes a minute and a half or more to run. Interestingly enough, if I take the SQL in the proc, and just run it outside the proc (setting the id of course), it returns instantly with either user id. It didn't even make a difference when I set the proc to run WITH RECOMPILE. Can someone give me a clue of what is going on here? I'm lost.
-
I have a fairly simple stored proc in one of my databases that does a couple of simple inner joins and returns a result set. The proc accepts a user id and returns a set of gl accounts to which that user can apply invoices and such. When I pass in an admin user id, all the records come back instantly, no problem. When I use a user id with less elevated permissions, the query takes a minute and a half or more to run. Interestingly enough, if I take the SQL in the proc, and just run it outside the proc (setting the id of course), it returns instantly with either user id. It didn't even make a difference when I set the proc to run WITH RECOMPILE. Can someone give me a clue of what is going on here? I'm lost.
What are you using to filter the user permissions, I hope you have a table that links the GL accounts to a userid/group. When the admin is used there is no filter so instant response, if you filter the results SQL has to do a scan of the table to locate the GL accounts, put and index on the linking fields (userid, accountid etc) in the various tables. There will almost certainly be indexes on the primary tables (userid on user table, accountid on GL table) but the linking table may not have any indexes.
Never underestimate the power of human stupidity RAH
-
I have a fairly simple stored proc in one of my databases that does a couple of simple inner joins and returns a result set. The proc accepts a user id and returns a set of gl accounts to which that user can apply invoices and such. When I pass in an admin user id, all the records come back instantly, no problem. When I use a user id with less elevated permissions, the query takes a minute and a half or more to run. Interestingly enough, if I take the SQL in the proc, and just run it outside the proc (setting the id of course), it returns instantly with either user id. It didn't even make a difference when I set the proc to run WITH RECOMPILE. Can someone give me a clue of what is going on here? I'm lost.
Without seeing the code for the stored proc it's hard to know what the problem is. Checking the indexes is good advice. If that doesn't work and you're still confused, you could try looking at the execution plan for the stored proc to see what it's actually doing. The SQL Server Query Analyzer lets you do this. Once you can see what the query is doing, you should have a better idea of how to fix it.
-
What are you using to filter the user permissions, I hope you have a table that links the GL accounts to a userid/group. When the admin is used there is no filter so instant response, if you filter the results SQL has to do a scan of the table to locate the GL accounts, put and index on the linking fields (userid, accountid etc) in the various tables. There will almost certainly be indexes on the primary tables (userid on user table, accountid on GL table) but the linking table may not have any indexes.
Never underestimate the power of human stupidity RAH
Actually, whether the user is admin or not, the security mechanism is the same. They have permissions to access certain branches, and those branches have access to GL accounts. I checked the indexes and the estimated execution plans and didn't see anything strange. After some fighting with the system, it appears that all I had to do was declare a variable inside the stored procedure, set it to the value of one of the parameters and then use it in my select clause. For whatever reason, this fixed the issue (and so far, it hasn't popped up again). I'm guessing that it somehow managed to cache an execution plan that was inappropriate for the data it had. It's still very strange.
-
I have a fairly simple stored proc in one of my databases that does a couple of simple inner joins and returns a result set. The proc accepts a user id and returns a set of gl accounts to which that user can apply invoices and such. When I pass in an admin user id, all the records come back instantly, no problem. When I use a user id with less elevated permissions, the query takes a minute and a half or more to run. Interestingly enough, if I take the SQL in the proc, and just run it outside the proc (setting the id of course), it returns instantly with either user id. It didn't even make a difference when I set the proc to run WITH RECOMPILE. Can someone give me a clue of what is going on here? I'm lost.