First, Hi to all I'm new to this forum.
Second, I don't know if standard SQL involves stored procedures, but anyway I'll post my doubt.
I have chessy little procedure to get a password from a login. login is a varchar
CREATE PROCEDURE getPassw
@.login as varchar
AS
SELECT T_Worker.pass
FROM T_Worker
WHERE T_Worker.login = @.login
If I try " exec getPassw 'abc' ", I never get anything. The data exists in the tables. If I do something like
CREATE PROCEDURE getPassw
/*@.login as varchar*/
AS
SELECT T_Worker.pass
FROM T_Worker
WHERE T_Worker.login = 'abc'
The password shows up : '456' .
If I remove the '@.' from the WHERE query, all columns are returned... ?!?!? :confused:
I running the commands on a Microsoft's SQL server. Thank you for your attention. Any help would be seriously apreciated.This post really belongs in the Microsoft SQL (http://www.dbforums.com/f7/) forum.
I think the only problem is that you didn't cut yourself enough rope... You need to make the parameter longer, like:CREATE PROCEDURE getPassw
@.login as varchar(50)
AS
SELECT T_Worker.pass
FROM T_Worker
WHERE T_Worker.login = @.login
RETURN-PatP|||AH!!! Something that simple... But it did work... Sorry not posting this in the right place :D
Showing posts with label second. Show all posts
Showing posts with label second. Show all posts
Thursday, March 8, 2012
Tuesday, March 6, 2012
A Second Set of Eyes
This is more of a "does anyone see something I'm missing" post versus a real problem.
What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentation, it does an INDEXDEFRAG. What I'd like to add to this is a piece that will iterate through all databases as well.
I'm close but no cigar. I've posted the code below. If anyone has any insight into where I may be going wrong, it would be greatly appreciated!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
DECLARE @.SQLSTRING VARCHAR(2000)
DECLARE @.DBNAME VARCHAR(64)
DECLARE @.tablename varchar(128)
DECLARE @.execstr varchar(255)
DECLARE @.objectid int
DECLARE @.indexid int
DECLARE @.frag decimal
DECLARE @.maxfrag decimal
DECLARE @.maxextfrag decimal
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0
SELECT @.maxextfrag = 40.0
DECLARE db CURSOR FOR
SELECT [NAME]
FROM [master].[dbo].[sysdatabases]
WHERE [NAME] NOT IN
('master', 'model', 'msdb', 'tempdb')
-- Declare a cursor.
--DECLARE tables CURSOR FOR
-- SELECT TABLE_NAME
-- FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal)
OPEN db
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the databases.
FETCH NEXT
FROM db
INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.execstr = 'USE ' + @.dbname + ';' + char(13)
PRINT @.execstr
EXEC (@.execstr)
-- Open the cursor.
OPEN tables
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH TABLERESULTS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @.tablename
END
-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables
SELECT @.SQLSTRING = 'INSERT INTO DBA_ADMIN.Fragmentation
(DatabaseName,
RunDate,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
SELECT '
SELECT @.SQLSTRING = @.SQLSTRING + @.DBNAME
SELECT @.SQLSTRING = @.SQLSTRING + ', getdate(),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag'
PRINT @.SQLSTRING
EXEC(@.SQLSTRING)
FETCH NEXT
FROM db
INTO @.DBNAME
END
CLOSE db
DEALLOCATE db
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor.
OPEN indexes
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%'
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
END
-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
--
-- Delete the temporary table.
DROP TABLE #fraglist
Again, thanks!!there r quite a few problems
1) u cannot change the database context by executing dynamic sql exec('use dbname').
2) the cursor tables is opened outside the loop and closed inside the loop
3) the table Fragmentation is not defined anywhere
ther could be more...|||I addressed the issue withthe tables curosr - works fine now.
The table Fragmentation is actually a permanent table, not a temp table.
..is there any way to actually change database context via SQL besides doing an "in line"|||..is there any way to actually change database context via SQL besides doing an "in line"
Undocumented, but do a search on ms_foreachdb (and ms_foreachtable).
Regards,
hmscott
PS. Undocumented means undocumented, ymmv.|||no. even sp_msforeachdb will not change the context permanently. all that it will do is provide u an option to execute sql in a different db and for all db. the same can be done by
exec ('use mydb select * from mytable')
What I'm doing is modifying a script I found in BOL. The script iterates through all the tables in a database and performs a SHOWCONTIG on all the tables. For those tables at a certain level of fragmentation, it does an INDEXDEFRAG. What I'd like to add to this is a piece that will iterate through all databases as well.
I'm close but no cigar. I've posted the code below. If anyone has any insight into where I may be going wrong, it would be greatly appreciated!
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET NOCOUNT ON
DECLARE @.SQLSTRING VARCHAR(2000)
DECLARE @.DBNAME VARCHAR(64)
DECLARE @.tablename varchar(128)
DECLARE @.execstr varchar(255)
DECLARE @.objectid int
DECLARE @.indexid int
DECLARE @.frag decimal
DECLARE @.maxfrag decimal
DECLARE @.maxextfrag decimal
-- Decide on the maximum fragmentation to allow for.
SELECT @.maxfrag = 30.0
SELECT @.maxextfrag = 40.0
DECLARE db CURSOR FOR
SELECT [NAME]
FROM [master].[dbo].[sysdatabases]
WHERE [NAME] NOT IN
('master', 'model', 'msdb', 'tempdb')
-- Declare a cursor.
--DECLARE tables CURSOR FOR
-- SELECT TABLE_NAME
-- FROM INFORMATION_SCHEMA.TABLES
-- WHERE TABLE_TYPE = 'BASE TABLE'
-- Create the table.
CREATE TABLE #fraglist (
ObjectName char(255),
ObjectId int,
IndexName char(255),
IndexId int,
Lvl int,
CountPages int,
CountRows int,
MinRecSize int,
MaxRecSize int,
AvgRecSize int,
ForRecCount int,
Extents int,
ExtentSwitches int,
AvgFreeBytes int,
AvgPageDensity int,
ScanDensity decimal,
BestCount int,
ActualCount int,
LogicalFrag decimal,
ExtentFrag decimal)
OPEN db
-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
-- Loop through all the databases.
FETCH NEXT
FROM db
INTO @.DBNAME
WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.execstr = 'USE ' + @.dbname + ';' + char(13)
PRINT @.execstr
EXEC (@.execstr)
-- Open the cursor.
OPEN tables
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @.tablename
WHILE @.@.FETCH_STATUS = 0
BEGIN
-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH TABLERESULTS, ALL_INDEXES')
FETCH NEXT
FROM tables
INTO @.tablename
END
-- Close and deallocate the cursor.
CLOSE tables
DEALLOCATE tables
SELECT @.SQLSTRING = 'INSERT INTO DBA_ADMIN.Fragmentation
(DatabaseName,
RunDate,
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag)
SELECT '
SELECT @.SQLSTRING = @.SQLSTRING + @.DBNAME
SELECT @.SQLSTRING = @.SQLSTRING + ', getdate(),
ObjectName,
ObjectId,
IndexName,
IndexId,
Lvl,
CountPages,
CountRows,
MinRecSize,
MaxRecSize,
AvgRecSize,
ForRecCount,
Extents,
ExtentSwitches,
AvgFreeBytes,
AvgPageDensity,
ScanDensity,
BestCount,
ActualCount,
LogicalFrag,
ExtentFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag'
PRINT @.SQLSTRING
EXEC(@.SQLSTRING)
FETCH NEXT
FROM db
INTO @.DBNAME
END
CLOSE db
DEALLOCATE db
-- Declare the cursor for the list of indexes to be defragged.
DECLARE indexes CURSOR FOR
SELECT ObjectName, ObjectId, IndexId, LogicalFrag
FROM #fraglist
WHERE LogicalFrag >= @.maxfrag
OR ExtentFrag >= @.maxextfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
-- Open the cursor.
OPEN indexes
-- Loop through the indexes.
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
WHILE @.@.FETCH_STATUS = 0
BEGIN
PRINT 'Executing DBCC INDEXDEFRAG (0, ' + RTRIM(@.tablename) + ',
' + RTRIM(@.indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@.frag)) + '%'
SELECT @.execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@.objectid) + ',
' + RTRIM(@.indexid) + ')'
EXEC (@.execstr)
FETCH NEXT
FROM indexes
INTO @.tablename, @.objectid, @.indexid, @.frag
END
-- Close and deallocate the cursor.
CLOSE indexes
DEALLOCATE indexes
--
-- Delete the temporary table.
DROP TABLE #fraglist
Again, thanks!!there r quite a few problems
1) u cannot change the database context by executing dynamic sql exec('use dbname').
2) the cursor tables is opened outside the loop and closed inside the loop
3) the table Fragmentation is not defined anywhere
ther could be more...|||I addressed the issue withthe tables curosr - works fine now.
The table Fragmentation is actually a permanent table, not a temp table.
..is there any way to actually change database context via SQL besides doing an "in line"|||..is there any way to actually change database context via SQL besides doing an "in line"
Undocumented, but do a search on ms_foreachdb (and ms_foreachtable).
Regards,
hmscott
PS. Undocumented means undocumented, ymmv.|||no. even sp_msforeachdb will not change the context permanently. all that it will do is provide u an option to execute sql in a different db and for all db. the same can be done by
exec ('use mydb select * from mytable')
Friday, February 24, 2012
A quest for a query
Hello all!
Could somebody please help me with the following query: I have a table with
3 colums. First column are just identity numbers. Second column contains
data in the following pattern: 2 rows of data, 1 null (empty) row and then
again 2 rows on data, 1 null row, etc. Third column is as now empty.
Now, the query should take each second "data" row of the second column
(cells in rows 2, 5, 8, 11,14, etc.) and copy their contents to the third
column but one row higher (column 2, row 2 should be copied to column 3,
row, c2 r5 to c3 r4, etc.) in a sort of partial transposing of the
table...I don't have an idea how to accomplish that.
Thank you in advance!
HrvojeTry,
update
a
set
a.colC = b.colB
from
t as a
inner join
t as b
on a.colA = b.colB - 1
and (b.colB = 2 or b.colB % 3 = 2)
AMB
"Hrvoje Vrbanc" wrote:
> Hello all!
> Could somebody please help me with the following query: I have a table wit
h
> 3 colums. First column are just identity numbers. Second column contains
> data in the following pattern: 2 rows of data, 1 null (empty) row and then
> again 2 rows on data, 1 null row, etc. Third column is as now empty.
> Now, the query should take each second "data" row of the second column
> (cells in rows 2, 5, 8, 11,14, etc.) and copy their contents to the third
> column but one row higher (column 2, row 2 should be copied to column 3,
> row, c2 r5 to c3 r4, etc.) in a sort of partial transposing of the
> table...I don't have an idea how to accomplish that.
> Thank you in advance!
> Hrvoje
>
>|||How does the data get populated in this table? The problem is that you
can't normally control the order in which IDENTITY values are allocated
so when you say "each second row" or Nth row or whatever, there may be
no guarantee that the rows numbered 1,2 or 3 are what you expected them
to be. Do not rely on the IDENTITY values being sequential unless you
populate all the data using the SET IDENTITY_INSERT option (in which
case, why use IDENTITY at all?).
The second issue is with the principle of this table design. Leaving
aside the difficulty with IDENTITY, my impression is that this is not a
correctly normalized table. I'll assume then that you intend to create
a new table and that the purpose of your query is to transform the data
into the correct format.
With that assumption in mind it's difficult to make much sense of your
final paragraph. What does "one row higher" mean? Rows should be
identifiable from keys not from row "position numbers".
In short, we'll need some more information to help you out: DDL, sample
data INSERTs and required end result. Please do include keys and
constraints if you post DDL. See this article for info on how to do
this:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hello!
It's a static flat table, no further population and no changes except the
one I'm searching the query for.
One row higher means the following: every row has the primary key that is an
ordinal number. I want to copy the data from one colum to another but to the
row with lower ordinal number.
Hrvoje
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110205802.879690.42060@.o13g2000cwo.googlegroups.com...
> How does the data get populated in this table? The problem is that you
> can't normally control the order in which IDENTITY values are allocated
> so when you say "each second row" or Nth row or whatever, there may be
> no guarantee that the rows numbered 1,2 or 3 are what you expected them
> to be. Do not rely on the IDENTITY values being sequential unless you
> populate all the data using the SET IDENTITY_INSERT option (in which
> case, why use IDENTITY at all?).
> The second issue is with the principle of this table design. Leaving
> aside the difficulty with IDENTITY, my impression is that this is not a
> correctly normalized table. I'll assume then that you intend to create
> a new table and that the purpose of your query is to transform the data
> into the correct format.
> With that assumption in mind it's difficult to make much sense of your
> final paragraph. What does "one row higher" mean? Rows should be
> identifiable from keys not from row "position numbers".
> In short, we'll need some more information to help you out: DDL, sample
> data INSERTs and required end result. Please do include keys and
> constraints if you post DDL. See this article for info on how to do
> this:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks!
I'll try!
Hrvoje
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5F3F369B-96E8-4611-AF48-75817E7B0399@.microsoft.com...
> Try,
> update
> a
> set
> a.colC = b.colB
> from
> t as a
> inner join
> t as b
> on a.colA = b.colB - 1
> and (b.colB = 2 or b.colB % 3 = 2)
>
> AMB
>
> "Hrvoje Vrbanc" wrote:
>
Could somebody please help me with the following query: I have a table with
3 colums. First column are just identity numbers. Second column contains
data in the following pattern: 2 rows of data, 1 null (empty) row and then
again 2 rows on data, 1 null row, etc. Third column is as now empty.
Now, the query should take each second "data" row of the second column
(cells in rows 2, 5, 8, 11,14, etc.) and copy their contents to the third
column but one row higher (column 2, row 2 should be copied to column 3,
row, c2 r5 to c3 r4, etc.) in a sort of partial transposing of the
table...I don't have an idea how to accomplish that.
Thank you in advance!
HrvojeTry,
update
a
set
a.colC = b.colB
from
t as a
inner join
t as b
on a.colA = b.colB - 1
and (b.colB = 2 or b.colB % 3 = 2)
AMB
"Hrvoje Vrbanc" wrote:
> Hello all!
> Could somebody please help me with the following query: I have a table wit
h
> 3 colums. First column are just identity numbers. Second column contains
> data in the following pattern: 2 rows of data, 1 null (empty) row and then
> again 2 rows on data, 1 null row, etc. Third column is as now empty.
> Now, the query should take each second "data" row of the second column
> (cells in rows 2, 5, 8, 11,14, etc.) and copy their contents to the third
> column but one row higher (column 2, row 2 should be copied to column 3,
> row, c2 r5 to c3 r4, etc.) in a sort of partial transposing of the
> table...I don't have an idea how to accomplish that.
> Thank you in advance!
> Hrvoje
>
>|||How does the data get populated in this table? The problem is that you
can't normally control the order in which IDENTITY values are allocated
so when you say "each second row" or Nth row or whatever, there may be
no guarantee that the rows numbered 1,2 or 3 are what you expected them
to be. Do not rely on the IDENTITY values being sequential unless you
populate all the data using the SET IDENTITY_INSERT option (in which
case, why use IDENTITY at all?).
The second issue is with the principle of this table design. Leaving
aside the difficulty with IDENTITY, my impression is that this is not a
correctly normalized table. I'll assume then that you intend to create
a new table and that the purpose of your query is to transform the data
into the correct format.
With that assumption in mind it's difficult to make much sense of your
final paragraph. What does "one row higher" mean? Rows should be
identifiable from keys not from row "position numbers".
In short, we'll need some more information to help you out: DDL, sample
data INSERTs and required end result. Please do include keys and
constraints if you post DDL. See this article for info on how to do
this:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hello!
It's a static flat table, no further population and no changes except the
one I'm searching the query for.
One row higher means the following: every row has the primary key that is an
ordinal number. I want to copy the data from one colum to another but to the
row with lower ordinal number.
Hrvoje
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1110205802.879690.42060@.o13g2000cwo.googlegroups.com...
> How does the data get populated in this table? The problem is that you
> can't normally control the order in which IDENTITY values are allocated
> so when you say "each second row" or Nth row or whatever, there may be
> no guarantee that the rows numbered 1,2 or 3 are what you expected them
> to be. Do not rely on the IDENTITY values being sequential unless you
> populate all the data using the SET IDENTITY_INSERT option (in which
> case, why use IDENTITY at all?).
> The second issue is with the principle of this table design. Leaving
> aside the difficulty with IDENTITY, my impression is that this is not a
> correctly normalized table. I'll assume then that you intend to create
> a new table and that the purpose of your query is to transform the data
> into the correct format.
> With that assumption in mind it's difficult to make much sense of your
> final paragraph. What does "one row higher" mean? Rows should be
> identifiable from keys not from row "position numbers".
> In short, we'll need some more information to help you out: DDL, sample
> data INSERTs and required end result. Please do include keys and
> constraints if you post DDL. See this article for info on how to do
> this:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>|||Thanks!
I'll try!
Hrvoje
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:5F3F369B-96E8-4611-AF48-75817E7B0399@.microsoft.com...
> Try,
> update
> a
> set
> a.colC = b.colB
> from
> t as a
> inner join
> t as b
> on a.colA = b.colB - 1
> and (b.colB = 2 or b.colB % 3 = 2)
>
> AMB
>
> "Hrvoje Vrbanc" wrote:
>
A QUERY THAT RUN ON DB2 THAT HAVE MORE PERFORMANCE THAN SQL SERVER 2000
The execution time for this query on DB2 v8.0 DBMS one second but I execute it on SQL SERVER 2000 is around 55 second
so how i can incease the performance for SQL server
SELECT ACC_KEY1,ACC_STATUS_LAST FROM PSSIG.CLNT_ACCOUNTS INNER JOIN PSSIG.CLNT_CUSTOMERS ON
PSSIG.CLNT_ACCOUNTS.CSTMR_OID = PSSIG.CLNT_CUSTOMERS.CSTMR_OID
WHERE (PSSIG.CLNT_CUSTOMERS.CSTMR_START_DT >= '1900-1-1 12:00:00') AND
(PSSIG.CLNT_CUSTOMERS.CSTMR_END_DT <= '2106-12-31 12:00:00') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 >= '0000000000000') AND
(PSSIG.CLNT_ACCOUNTS.ACC_KEY1 <= '9999999999999') AND
(PSSIG.CLNT_ACCOUNTS.ACC_STATUS_LAST = 5 ) AND
ACC_KEY1 > '0' ORDER BY ACC_KEY1
Note 1: value 5 exist in most of rows about ( 999999/1000000 ) from the table rows count
Note 2: the number of rows in each table around 15000000
Note 3: I used the same index structure for both DB2 and SQL server 2000
Note 4: I used some other feature in DB2 that increase the performance but I did not
found the alternative for it in SQL server 2000 :
a- cardinality varies at run time feature
b- include column in index instead of use compound index for
( ACC_KEY1 ,ACC_STATUS_LAST ) columns
Note 5 : Enable reverse scan for index
Um, why are you using strings to store the ACC_KEY1? Numeric fields are much faster.
I would suggest that you drop all your indexes that relate to that query. Then run the Database Engine Tuning Advisor (or whatever its called in SQL 2000) to determine what the right indexes are. Unless you know SQL Server intimately, it can generate better indexes than you can by hand.
Jonathan
|||
thank you for you advice , i use the tuuning wizard but it did not improve the performance
- and acc_key1 could contain a letter so it must be a string
|||You use the same indexes, but what does those indexes look like?
What is the volume to be returned? Is the expected output close to a million rows? (all the '5's)
How do you measure the time? Do you look at the server for the time it takes to resolve the query, or do you measure at the 'end-point'? (ie if you select... and wait until a million rows has been drawn on the screen, or similar)
/Kenneth
Subscribe to:
Posts (Atom)