I've inherited Database for a Web Application (with no support) that
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-
Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:
> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>
|||My guess is that you do have users in your database named in a way so the names can be converted to
int. You can see what usernames exists in the database thought the sys.database_principals catalog
view. If my assumption is correct, they written a pretty crappy function, which need to be fixed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...
> I've inherited Database for a Web Application (with no support) that makes heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I receive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute 'SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, when this function is
> called from the Web Application, it returns a 'username' defined in the dbo.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how that would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
Showing posts with label least. Show all posts
Showing posts with label least. Show all posts
Thursday, March 22, 2012
CURRENT_USER issue
I've inherited Database for a Web Application (with no support) that
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:
> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>|||My guess is that you do have users in your database named in a way so the names can be converted to
int. You can see what usernames exists in the database thought the sys.database_principals catalog
view. If my assumption is correct, they written a pretty crappy function, which need to be fixed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...
> I've inherited Database for a Web Application (with no support) that makes heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I receive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute 'SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, when this function is
> called from the Web Application, it returns a 'username' defined in the dbo.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how that would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:
> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>|||My guess is that you do have users in your database named in a way so the names can be converted to
int. You can see what usernames exists in the database thought the sys.database_principals catalog
view. If my assumption is correct, they written a pretty crappy function, which need to be fixed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...
> I've inherited Database for a Web Application (with no support) that makes heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I receive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute 'SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, when this function is
> called from the Web Application, it returns a 'username' defined in the dbo.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how that would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
CURRENT_USER issue
I've inherited Database for a Web Application (with no support) that
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:
> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>|||My guess is that you do have users in your database named in a way so the na
mes can be converted to
int. You can see what usernames exists in the database thought the sys.datab
ase_principals catalog
view. If my assumption is correct, they written a pretty crappy function, wh
ich need to be fixed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...[v
bcol=seagreen]
> I've inherited Database for a Web Application (with no support) that makes
heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I re
ceive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation
above.
> I understand what the above function is trying to do, but when I execute '
SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, whe
n this function is
> called from the Web Application, it returns a 'username' defined in the db
o.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the
UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how tha
t would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-[/vbcol]
makes heavy use of the following function, at least in the report views
that have been defined:
ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
RETURNS bit
AS
BEGIN
DECLARE @.UserId int
SET @.UserId = CAST(CURRENT_USER AS int)
DECLARE @.SysAdmin bit
SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
-- Returns 1 if SysAdmin, 0 if normal user
RETURN @.SysAdmin
END
If you use the web application, everything runs fine.
If you try to access the report view from with SQL Management Studio, I
receive an error about not being able to convert an NVARCHAR ('dbo') to
an INT on the CAST operation above.
I understand what the above function is trying to do, but when I execute
'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
the result. Somehow, when this function is called from the Web
Application, it returns a 'username' defined in the dbo.Users table that
gets converted to an INT, which is supposed to be the exact value stored
in the UserID column for that particular record.
Sample user record:
UserID = 10001
Username = john.doe@.example.com
SystemAdministrator = 1 (true)
The function should return true in this instance, but I don't know how
that would be possible. It doesn't seem like the function should work at
all.
Any help would be greatly appreciated.
-={ Kyle K. }=-Hi,
Instead of Current_user, can you please use function SYSTEM_USER.
Please write back if it works.
Thanks
Hari
SQL Server MVP
"Kyle K." wrote:
> I've inherited Database for a Web Application (with no support) that
> makes heavy use of the following function, at least in the report views
> that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I
> receive an error about not being able to convert an NVARCHAR ('dbo') to
> an INT on the CAST operation above.
> I understand what the above function is trying to do, but when I execute
> 'SELECT CURRENT_USER' from within SQL Management Studio, I get 'dbo' as
> the result. Somehow, when this function is called from the Web
> Application, it returns a 'username' defined in the dbo.Users table that
> gets converted to an INT, which is supposed to be the exact value stored
> in the UserID column for that particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how
> that would be possible. It doesn't seem like the function should work at
> all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-
>|||My guess is that you do have users in your database named in a way so the na
mes can be converted to
int. You can see what usernames exists in the database thought the sys.datab
ase_principals catalog
view. If my assumption is correct, they written a pretty crappy function, wh
ich need to be fixed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Kyle K." <SKyleK@.Frontiernet.net> wrote in message news:55_Ng.498$Ka1.99@.news01.roc.ny...[v
bcol=seagreen]
> I've inherited Database for a Web Application (with no support) that makes
heavy use of the
> following function, at least in the report views that have been defined:
>
> ALTER FUNCTION [dbo].[fn_UserIsSysAdmin] ()
> RETURNS bit
> AS
> BEGIN
> DECLARE @.UserId int
> SET @.UserId = CAST(CURRENT_USER AS int)
> DECLARE @.SysAdmin bit
> SELECT @.SysAdmin = SystemAdministrator FROM Users WHERE UserId = @.UserId
> -- Returns 1 if SysAdmin, 0 if normal user
> RETURN @.SysAdmin
> END
>
> If you use the web application, everything runs fine.
> If you try to access the report view from with SQL Management Studio, I re
ceive an error about not
> being able to convert an NVARCHAR ('dbo') to an INT on the CAST operation
above.
> I understand what the above function is trying to do, but when I execute '
SELECT CURRENT_USER'
> from within SQL Management Studio, I get 'dbo' as the result. Somehow, whe
n this function is
> called from the Web Application, it returns a 'username' defined in the db
o.Users table that gets
> converted to an INT, which is supposed to be the exact value stored in the
UserID column for that
> particular record.
> Sample user record:
> UserID = 10001
> Username = john.doe@.example.com
> SystemAdministrator = 1 (true)
> The function should return true in this instance, but I don't know how tha
t would be possible. It
> doesn't seem like the function should work at all.
> Any help would be greatly appreciated.
> -={ Kyle K. }=-[/vbcol]
Sunday, March 11, 2012
Curious performance issue when running a query
Hi,
I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.
The table has approximately 5 million rows and includes the following
columns:
DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
Title (VARCHAR(255), NONCLUSTERED)
If I issue the following query:
SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
it takes about 23 seconds to return the 481 hits.
The execution plan shows a non-clustered index scan being performed on
the Title index (returning 481 rows) and a non-clustered index scan on
the IsRecord index (returning 4.9 million rows). These are then merged
in a hash match/inner join operation.
The Title index scan has an estimated row size of 41 and an I/O cost
of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
accounts for a further 52% of the cose with the SELECT at the head of
the plan listed as 0% cost.
If I issue the following query:
SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
it takes about 12 seconds to return the 481 hits and consists solely
of a non-clustered index scan of the Title Index.
Again the Title index scan has an estimated row size of 41 and an I/O
cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
the plan is attributed the other 22% of the cost.
All this is fine, however when I issue the following query:
SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
'%process%'
it takes 1 minute 50 seconds to run the query. The execution plans
shows that a clustered index scan is occurring and this accounts for
96% of the cost. The estimated row size is 463 and the I/O cost is
111.
What on earth is going on here. I can understand the need to scan the
Title index because of the wildcards, but why on earth would the query
perform a scan of the clustered (primary key) index? And what is going
on with the row size and I/O cost?
All the indexes and statistics are up to date, so I am at a complete
loss to explain what is going on here. Can anyone explain why the 3rd
query is so much slower (and possibly suggest a way to improve the
performance)/
Thanks
Paul Mateer
Meridio Limted
I am at a complete loss to explain what is happening here,Paul
If you the query frequently I 'd recomend you to create covering indexes on
all columns that participate with the query.
Also try to run WHERE condition like 'process%' this one would not perevent
the optimyzer for using index.
"Paul Mateer" <p.mateer@.meridio.com> wrote in message
news:424f2ade.0312030409.f602b09@.posting.google.com...
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
>
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
>
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,|||The problem is in the fact that SQL-Server does not have appropriate
statistics to determine how many rows will qualify based on the
predicate "Title LIKE '%process%'". In those cases, SQL-Server will
assume the worst case scenario, which basically excludes BookMark
Lookups.
For your first query, SQL-Server is using a very smart query plan. It
estimated that it would be faster to scan two indexes and hash the
result (23 seconds as it turns out) than to perform a clustered index
scan (110 seconds as it turns out). So that plan was pretty good.
I suspect that because of the extra column in the selection list, for
your 'slow' query SQL-Server is not considering index intersection. If
that is the case/cause, then IMO that is a flaw in the query optimizer.
Because of this 'lack of statistics' issue, SQL-Server is likely to
choose a suboptimal query plan. If you have information that SQL-Server
doesn't (for example, you know that the query will result in a few rows,
and not in all rows), then you might consider giving SQL-Server a hint.
If the query returns only 500 rows out of 5 million, then I would try
the following query, because I expect runs in less than 15 seconds:
SELECT DocID, Title, IsRecord
FROM DocTable (index=NameOfIndexOnTitleColumn)
WHERE Title LIKE '%process%'
Hope this helps,
Gert-Jan
Paul Mateer wrote:
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,
I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.
The table has approximately 5 million rows and includes the following
columns:
DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
Title (VARCHAR(255), NONCLUSTERED)
If I issue the following query:
SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
it takes about 23 seconds to return the 481 hits.
The execution plan shows a non-clustered index scan being performed on
the Title index (returning 481 rows) and a non-clustered index scan on
the IsRecord index (returning 4.9 million rows). These are then merged
in a hash match/inner join operation.
The Title index scan has an estimated row size of 41 and an I/O cost
of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
accounts for a further 52% of the cose with the SELECT at the head of
the plan listed as 0% cost.
If I issue the following query:
SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
it takes about 12 seconds to return the 481 hits and consists solely
of a non-clustered index scan of the Title Index.
Again the Title index scan has an estimated row size of 41 and an I/O
cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
the plan is attributed the other 22% of the cost.
All this is fine, however when I issue the following query:
SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
'%process%'
it takes 1 minute 50 seconds to run the query. The execution plans
shows that a clustered index scan is occurring and this accounts for
96% of the cost. The estimated row size is 463 and the I/O cost is
111.
What on earth is going on here. I can understand the need to scan the
Title index because of the wildcards, but why on earth would the query
perform a scan of the clustered (primary key) index? And what is going
on with the row size and I/O cost?
All the indexes and statistics are up to date, so I am at a complete
loss to explain what is going on here. Can anyone explain why the 3rd
query is so much slower (and possibly suggest a way to improve the
performance)/
Thanks
Paul Mateer
Meridio Limted
I am at a complete loss to explain what is happening here,Paul
If you the query frequently I 'd recomend you to create covering indexes on
all columns that participate with the query.
Also try to run WHERE condition like 'process%' this one would not perevent
the optimyzer for using index.
"Paul Mateer" <p.mateer@.meridio.com> wrote in message
news:424f2ade.0312030409.f602b09@.posting.google.com...
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
>
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
>
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,|||The problem is in the fact that SQL-Server does not have appropriate
statistics to determine how many rows will qualify based on the
predicate "Title LIKE '%process%'". In those cases, SQL-Server will
assume the worst case scenario, which basically excludes BookMark
Lookups.
For your first query, SQL-Server is using a very smart query plan. It
estimated that it would be faster to scan two indexes and hash the
result (23 seconds as it turns out) than to perform a clustered index
scan (110 seconds as it turns out). So that plan was pretty good.
I suspect that because of the extra column in the selection list, for
your 'slow' query SQL-Server is not considering index intersection. If
that is the case/cause, then IMO that is a flaw in the query optimizer.
Because of this 'lack of statistics' issue, SQL-Server is likely to
choose a suboptimal query plan. If you have information that SQL-Server
doesn't (for example, you know that the query will result in a few rows,
and not in all rows), then you might consider giving SQL-Server a hint.
If the query returns only 500 rows out of 5 million, then I would try
the following query, because I expect runs in less than 15 seconds:
SELECT DocID, Title, IsRecord
FROM DocTable (index=NameOfIndexOnTitleColumn)
WHERE Title LIKE '%process%'
Hope this helps,
Gert-Jan
Paul Mateer wrote:
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,
Curious performance issue when running a query
Hi,
I have been running some queries against a table in a my database and
have noted an odd (at least it seems odd to me) performance issue.
The table has approximately 5 million rows and includes the following
columns:
DocID (INTEGER, PRIMARY KEY, CLUSTERED)
IsRecord (INTEGER, NONCLUSTERED)
Title (VARCHAR(255), NONCLUSTERED)
If I issue the following query:
SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
it takes about 23 seconds to return the 481 hits.
The execution plan shows a non-clustered index scan being performed on
the Title index (returning 481 rows) and a non-clustered index scan on
the IsRecord index (returning 4.9 million rows). These are then merged
in a hash match/inner join operation.
The Title index scan has an estimated row size of 41 and an I/O cost
of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
accounts for a further 52% of the cose with the SELECT at the head of
the plan listed as 0% cost.
If I issue the following query:
SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
it takes about 12 seconds to return the 481 hits and consists solely
of a non-clustered index scan of the Title Index.
Again the Title index scan has an estimated row size of 41 and an I/O
cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
the plan is attributed the other 22% of the cost.
All this is fine, however when I issue the following query:
SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
'%process%'
it takes 1 minute 50 seconds to run the query. The execution plans
shows that a clustered index scan is occurring and this accounts for
96% of the cost. The estimated row size is 463 and the I/O cost is
111.
What on earth is going on here. I can understand the need to scan the
Title index because of the wildcards, but why on earth would the query
perform a scan of the clustered (primary key) index? And what is going
on with the row size and I/O cost?
All the indexes and statistics are up to date, so I am at a complete
loss to explain what is going on here. Can anyone explain why the 3rd
query is so much slower (and possibly suggest a way to improve the
performance)/
Thanks
Paul Mateer
Meridio Limted
I am at a complete loss to explain what is happening here,Paul
If you the query frequently I 'd recomend you to create covering indexes on
all columns that participate with the query.
Also try to run WHERE condition like 'process%' this one would not perevent
the optimyzer for using index.
"Paul Mateer" <p.mateer@.meridio.com> wrote in message
news:424f2ade.0312030409.f602b09@.posting.google.co m...
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
>
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
>
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,|||Hi Uri. Thanks for the reply. I tried your suggestion of a covering
index and it worked.
I have a new problem however. In order to keep things simple the queries
that I posted were simplified versions of the actual query that I need
to execute. I cannot create a covering index for all of the required
columns because SQL 2000 limits the key length to 900 bytes and the
total length of all the column data involved in the query is over 1000
bytes in length.
Any suggestions on how to resolve this problem would be greatly
appreciated.
Paul Mateer
Meridio Limited
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||The problem is in the fact that SQL-Server does not have appropriate
statistics to determine how many rows will qualify based on the
predicate "Title LIKE '%process%'". In those cases, SQL-Server will
assume the worst case scenario, which basically excludes BookMark
Lookups.
For your first query, SQL-Server is using a very smart query plan. It
estimated that it would be faster to scan two indexes and hash the
result (23 seconds as it turns out) than to perform a clustered index
scan (110 seconds as it turns out). So that plan was pretty good.
I suspect that because of the extra column in the selection list, for
your 'slow' query SQL-Server is not considering index intersection. If
that is the case/cause, then IMO that is a flaw in the query optimizer.
Because of this 'lack of statistics' issue, SQL-Server is likely to
choose a suboptimal query plan. If you have information that SQL-Server
doesn't (for example, you know that the query will result in a few rows,
and not in all rows), then you might consider giving SQL-Server a hint.
If the query returns only 500 rows out of 5 million, then I would try
the following query, because I expect runs in less than 15 seconds:
SELECT DocID, Title, IsRecord
FROM DocTable (index=NameOfIndexOnTitleColumn)
WHERE Title LIKE '%process%'
Hope this helps,
Gert-Jan
Paul Mateer wrote:
> Hi,
> I have been running some queries against a table in a my database and
> have noted an odd (at least it seems odd to me) performance issue.
> The table has approximately 5 million rows and includes the following
> columns:
> DocID (INTEGER, PRIMARY KEY, CLUSTERED)
> IsRecord (INTEGER, NONCLUSTERED)
> Title (VARCHAR(255), NONCLUSTERED)
> If I issue the following query:
> SELECT DocID, IsRecord FROM DocTable WHERE Title LIKE '%process%'
> it takes about 23 seconds to return the 481 hits.
> The execution plan shows a non-clustered index scan being performed on
> the Title index (returning 481 rows) and a non-clustered index scan on
> the IsRecord index (returning 4.9 million rows). These are then merged
> in a hash match/inner join operation.
> The Title index scan has an estimated row size of 41 and an I/O cost
> of 9.82 (cost is 27%). The IsRecord index scan has an estimated row
> size of 33 and an I/O cost of 6.32 (cost is 21%). The Hash Match
> accounts for a further 52% of the cose with the SELECT at the head of
> the plan listed as 0% cost.
> If I issue the following query:
> SELECT DocID, Title FROM DocTable WHERE Title LIKE '%process%'
> it takes about 12 seconds to return the 481 hits and consists solely
> of a non-clustered index scan of the Title Index.
> Again the Title index scan has an estimated row size of 41 and an I/O
> cost of 9.82 ans it's cost is listed as 78%. The SELECT at the head of
> the plan is attributed the other 22% of the cost.
> All this is fine, however when I issue the following query:
> SELECT DocID, Title, IsRecord FROM DocTable WHERE Title LIKE
> '%process%'
> it takes 1 minute 50 seconds to run the query. The execution plans
> shows that a clustered index scan is occurring and this accounts for
> 96% of the cost. The estimated row size is 463 and the I/O cost is
> 111.
> What on earth is going on here. I can understand the need to scan the
> Title index because of the wildcards, but why on earth would the query
> perform a scan of the clustered (primary key) index? And what is going
> on with the row size and I/O cost?
> All the indexes and statistics are up to date, so I am at a complete
> loss to explain what is going on here. Can anyone explain why the 3rd
> query is so much slower (and possibly suggest a way to improve the
> performance)/
> Thanks
> Paul Mateer
> Meridio Limted
> I am at a complete loss to explain what is happening here,
Saturday, February 25, 2012
cube limit
Hi all,
I am new to SSAS with SQL2005SP2. Currently, I have a fact table with almost 10 million records. In this table, I have at least 27 fields which store monthly data for the past 27 months along with some other fields. At first, I plan to make those 27 fields to one field and add another field like period to capture the year and month information so I can link to my time dimension table. Of course, this makes the new table with 270 million record counts. It took forever to process the cube. If I add more dimenstions into this cube, I got an error msg saying it exceed the limits. I am trying to find out the limit but did not get anywhere. Does anyone know the max dimensions is allowed in a cube or the max intersection can be created for a cube?
wenchi
did you patrition your cube?
what exactly error message you have got?
how many dimensions (attribute hierarchies) has the cube?
|||I think the theoretical limit is 2 billion attributes in a cube. If you could answer Vladimir's questions we might be able to help identify your issue.
cube limit
Hi all,
I am new to SSAS with SQL2005SP2. Currently, I have a fact table with almost 10 million records. In this table, I have at least 27 fields which store monthly data for the past 27 months along with some other fields. At first, I plan to make those 27 fields to one field and add another field like period to capture the year and month information so I can link to my time dimension table. Of course, this makes the new table with 270 million record counts. It took forever to process the cube. If I add more dimenstions into this cube, I got an error msg saying it exceed the limits. I am trying to find out the limit but did not get anywhere. Does anyone know the max dimensions is allowed in a cube or the max intersection can be created for a cube?
wenchi
did you patrition your cube?
what exactly error message you have got?
how many dimensions (attribute hierarchies) has the cube?
|||I think the theoretical limit is 2 billion attributes in a cube. If you could answer Vladimir's questions we might be able to help identify your issue.
Subscribe to:
Posts (Atom)