August 1, 2002 at 8:25 am
I have an application window that takes 55 seconds to load with SQL2K and only 3 seconds when I change the database compatibility level back to 7.0.
Our data rows all contain a SiteID and each user is setup in a SiteSecurity table that contains a record with the user's granted Sites and their username. I have modified the views to take a direct subset of the underlying table filtered by SELECT * FROM TableName WHERE (SiteID IN (SELECT SiteID FROM SiteSecurity WHERE LoginName = SYSTEM_USER)). The odd thing is that Site1 users are fast. Users with access to all sites are fast, but users for sites above Site1 that don't have permission to site1 are very slow. As soon as I grant them access to site 1 in addition to their existing site the problem goes away. All users have access to Site0 to handle viewing of unprimed inserted records that don't have their site yet.
The application window displays hotel availability and can make up to 1000 queries to populate the availability grid. Any idea on why 7.0 handled this fine and 2K doesn’t like it? Most of the application is much faster on 2000.
Thanks,
Dane
August 1, 2002 at 8:54 am
Could you post the query plans running in both modes?
Andy
August 1, 2002 at 9:07 am
Is this what you're looking for? The window procedure may call this several hundred times based on settings for the user.
SQL2000
|--Nested Loops(Left Semi Join, WHERE:([t_SumInventory].[SiteID]=[SiteSecurity].[SiteID]))
|--Clustered Index Seek(OBJECT:([TPI].[dbo].[t_SumInventory].[SUMITypeInOutDate]), SEEK:([t_SumInventory].[Type] <= 'Overbooked'), WHERE:([t_SumInventory].[Type]<'Overbooked' OR ([t_SumInventory].[CheckInDate]<='Aug 22 2002 12:00AM' AND ([t_SumInve
|--Table Spool
|--Index Scan(OBJECT:([TPI].[dbo].[SiteSecurity].[SSLoginNameSiteID]), WHERE:(Convert([SiteSecurity].[LoginName])=suser_sname(NULL)))
SQL7
|--Nested Loops(Left Semi Join, WHERE:([t_SumInventory].[SiteID]=[SiteSecurity].[SiteID]))
|--Clustered Index Seek(OBJECT:([TPI].[dbo].[t_SumInventory].[SUMITypeInOutDate]), SEEK:([t_SumInventory].[Type] <= 'Overbooked'), WHERE:([t_SumInventory].[Type]<'Overbooked' OR ([t_SumInventory].[CheckInDate]<='Aug 22 2002 12:00AM' AND ([t_SumInve
|--Table Spool
|--Index Seek(OBJECT:([TPI].[dbo].[SiteSecurity].[SSLoginNameSiteID]), SEEK:([SiteSecurity].[LoginName]=Convert(suser_sname(NULL))) ORDERED FORWARD)
August 1, 2002 at 9:35 am
In SQL2K there is a table scan on the 'sitesecurity' table, whereas in SQL7 it's a seek.
I do not know what the exact cause is (let alone the solution), but I can see two possible causes :
1. An index was not created in SQL2K, which seems odd, since it is the same database.
2. The difference is in the value that is being converted.
SQL7 : converts the parameter that is supplied.
>> ([SiteSecurity].[LoginName]=Convert(suser_sname(NULL)))
SQL2K : converts the contents of the LoginName field
>> (Convert([SiteSecurity].[LoginName])=suser_sname(NULL)))
My bet is on the second reason, so I guess that there is a difference in the way SQL2K converts values between different types.
Can you try adding the conversion logic to the query itself, just as a test?
WHERE LoginName = CONVERT(SYSTEM_USER)
or something similar...
August 1, 2002 at 10:00 am
I changed from LoginName = SYSTEM_USER to LoginName = CONVERT(varchar,SYSTEM_USER) and that did the trick! Any ideas why that solved the problem?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply