Any ideas on how to optimize this query?
-
Hello,
anyone ideas on how to optimize this query?
I have one table having 2 millions of rows (representing an object). Each row uses three other tables to calculate permission on read, write access. The first table has 5 million rows, the second has 500 rows and the last one has 50 million rows.
My current approach is to SELECT from the first table the rows that I want, hopefully this will be around 1000, on a BAD filter this number will be 2 million, but 99% of the time it will be below 100. And put theese rows in a #temp table.
For each of those rows I will then calculate security.
If a user does not hade read permission I will have to return NULL. Permission can be set on Field level on the object above. Therefore a user may get a result looking like Field1, NULL, Field2, NULL. This is done using CASE WHEN 1 THEN Field1 CASE 0 THEN NULL END on each of the fields from the #temp table. The User is based on a uniqueidentifier specified in the query.
Ok thats the scenario, any ideas on how to make this scenario run faster?
/Joel
-
Can you post a sample table structure?
Leo
-
Uhmm quite large/complex but simplified they look like this:
ObjectTable (2 millions)
Uniqueidentifier, int, int, varchar(50),int, int, varchar(50) etc. mostly 200 columns.
PermissionTable1 (2 millions) Each object has one row in this table
Uniqueidentifier, Criteria1,Criteria2,Criteria3,Criteria4,Criteria5,Criteria6
PermissionTable2 (500 rows) Each field here is found in the Object table above. This table is equal to number of user criterias / criterias in permissiontable1
Criteria10, Criteria12, Status, Field1, Field2 etc.
PermissionTable3 (20 million) Each user may have different usercriterias for each object (in the example 10/object)
Uniqueidentifier (userid),Uniqueidentifier UserCriteria, Uniqueidentifier ObjectID
-
Do you have any indexes on these tables, and what does the query look like?
-
Yes I have indexes on all Primary keys ( as default by SQL server ). And the primary keys are the columns that I use to link tables together.
The query is 800 rows long
But it is split into two queries.
1) Select all fields into a #Temp table. Applying the WHERE clause to reduce number of object to check security on. This is done by a really simple query e.g.
SELECT Field1 INTO #temp FROM Table1 WHERE Field1=1
2) Use the objects from the temporary table to check security on by using the three constraints tables as explained above.
This query is quite complex but looks something like this:
SELECT
Case (PermissionTable1.Criteria1&1) -- Bit operation to mask read permission
WHEN 1 THEN #temp.Field1 -- If read permission then show field1 from #temp
WHEN 0 THEN NULL -- If not then return NULL
etc. for all field (up to 200)
JOIN
Tables using indexes
What I would like some input on is:
How slow is Bitoperation and Case, is there any other obvious ways to solve my problem without using them?
How slow could the use of a #temp table be when many users execute queries?
/Thanks
-
Bit operations and case statements are not that slow, and any other way of doing it without might prove to be more time consuming.
Temp tables could also slow you down, if they return big result sets. You could consider parameterized views maybe for returning subsets of huge tables, if that is possible.
Looking at your query above, I would definitely create an index on Field1 (if that is not your primary key already)
Hope this helps.
Martin.
-
If your temp table has 1000 rows and is referenced frequently, you might try using a table variable instead of a temp table. This will keep your data directly in memory and can sometimes increase performance significantly. It may not help at all, but it is worth a shot.
Paul
-
Thanks for the input!
I will try Views and Table Variable and see if any of them are possible solutions.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply