Max Query: declare

Showing posts with label declare. Show all posts

Monday, March 26, 2012

help with sp_executesql

hi i'm trying to do this

DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(2000)
DECLARE @.VARP Nvarchar(1000)

SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 char(20)'
SET @.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1 NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2) DECLARE @.nCHB1 NUMERIC(12,2) '

@.VARR1 AND @.VARR AND @.VARP is similar to @.VARC. At the end i trying to open a Cursor.. all the query are inside in @.varc + @.varr + @.var1 + @.varp

WHEN I EXECUTE THIS

EXEC('EXEC sp_executesql ' + @.VARC + @.VARR + @.VARR1 + @.VARP )

THE RESULT IS

Procedure 'sp_executesql' expects parameter '@.statement', which was not supplied.

WHY?

DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(4000) -- nvarchar(max) SQL 2005

DECLARE @.VARP Nvarchar(1000)

SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 char(20)'
SET

@.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1

NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2)

DECLARE @.nCHB1 NUMERIC(12,2) '

@.VARR1

AND @.VARR AND @.VARP is similar to @.VARC. At the end i trying to open a

Cursor.. all the query are inside in @.varc + @.varr + @.var1 + @.varp

WHEN I EXECUTE THIS

set @.Varc = 'EXEC sp_executesql' + @.VARC + @.VARR + @.VARR1 + @.VARP

EXEC(@.Varc)|||

the real problem is that @.varc and @.VARR1 AND @.VARR AND @.VARP has more that 4000 chars... and i know that sp_executesql Store only accept ntext, nvarchar, image.... i using sql server 2000

i'm trying to do this

create PROCEDURE store_xxx1
(
@.i_AMLCBANKNU INT,
@.i_PERIOD INT,
@.dPRC DATETIME,
@.sDay INT,
@.eDay INT,
@.i_AMLDPERIFR DATETIME,
@.i_AMLDPERITO DATETIME
)
AS
-- SET OPTION DATFMT = *MDY, DATSEP = *DASH
BEGIN
DECLARE @.sSTM varchar(8000)--8132
DECLARE @.sSTMAUX varchar(5000)--8132
DECLARE @.VARR Nvarchar(2500)
DECLARE @.VARR1 Nvarchar(2500)
DECLARE @.VARC Nvarchar(2000)
DECLARE @.VARP Nvarchar(1000)

DECLARE @.sQTY VARCHAR(1024)
DECLARE @.sCSH VARCHAR(1024)
DECLARE @.sNCH VARCHAR(1024)
DECLARE @.sTOT VARCHAR(1024)
DECLARE @.sCHB VARCHAR(1024)
DECLARE @.sTGP VARCHAR(1024)
DECLARE @.nBKN NUMERIC(3,0) --DEFAULT 1
DECLARE @.nAPP NUMERIC(2,0)-- DEFAULT 0
DECLARE @.nACT NUMERIC(1,0)-- DEFAULT 0
DECLARE @.nACC NUMERIC(12,0) --DEFAULT 0
DECLARE @.nQTY NUMERIC(9,0) --DEFAULT 0
DECLARE @.nGRP NUMERIC(2,0) --DEFAULT 0
DECLARE @.nCSH NUMERIC(12,2) --DEFAULT 0
DECLARE @.nNCH NUMERIC(12,2) --DEFAULT 0
DECLARE @.nTOT NUMERIC(12,2) --DEFAULT 0
DECLARE @.nCHB NUMERIC(12,2) --DEFAULT 0
DECLARE @.nTGP NUMERIC(12,2) --DEFAULT 0
DECLARE @.sSEL VARCHAR(96) --DEFAULT 'SELECT AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA, '
DECLARE @.iDay INT
DECLARE @.xDay INT
DECLARE @.v_contador INT
DECLARE @.at_end int --default 0

-- DECLARE Not_Found CONDITION FOR SQLSTATE '02000'
-- DECLARE C1 DYNAMIC SCROLL CURSOR FOR sSQLSTM
DECLARE @.C1 CURSOR
--SETEANDO LA VARIABLES DEFAULT
SET @.nBKN =@.i_AMLCBANKNU
SET @.nAPP =0
SET @.nACT =0
SET @.nACC =0
SET @.nQTY =0
SET @.nGRP =0
SET @.nCSH =0
SET @.nNCH =0
SET @.nTOT =0
SET @.nCHB =0
SET @.nTGP =0
SET @.sQTY = ''
SET @.sSTM = ''
SET @.sQTY = ''
SET @.sCSH = ''
SET @.sNCH = ''
SET @.sTOT = ''
SET @.sCHB = ''
SET @.sTGP = ''
SET @.VARR = ''
SET @.sSEL= 'SELECT AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA, '
SET @.at_end=0

SET @.VARR1=''
SET @.VARP=''
--DECLARE CONTINUE HANDLER FOR Not_Found
SET @.at_end = 1
SET @.iDay = @.sDay

IF (@.sDay > @.eDay)
BEGIN
SET @.xDay = @.eDay
SET @.eDay = 31 + @.eDay
END

WHILE (@.iDay < @.eDay)
BEGIN
SET @.sQTY = @.sQTY + 'AMLNQTYT' + CONVERT(VARCHAR(2),@.iDay) + '+'
SET @.sCSH = @.sCSH + 'AMLNCSHT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sNCH = @.sNCH + 'AMLNNCHT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sTOT = @.sTOT + 'AMLNTOTT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sCHB = @.sCHB + 'AMLNCHBT' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.sTGP = @.sTGP + 'AMLTOGRP' + CONVERT(VARCHAR(2),@.iDay)+ '+'
SET @.iDay = @.iDay + 1
IF (@.iDay =32)
BEGIN
SET @.iDay=01
SET @.eDay=@.xDay
END
END

SET @.sQTY = ((@.sQTY + ('AMLNQTYT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sCSH = ((@.sCSH + ('AMLNCSHT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sNCH = ((@.sNCH + ('AMLNNCHT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sTOT = ((@.sTOT + ('AMLNTOTT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sCHB = ((@.sCHB + ('AMLNCHBT' + CONVERT(VARCHAR(2),@.iDay))) + ', ')
SET @.sTGP = ((@.sTGP + ('AMLTOGRP' + CONVERT(VARCHAR(2),@.iDay))) + ' ')
SET @.VARR = @.sSEL + @.sQTY + @.sCSH + @.sNCH + @.sTOT + @.sCHB + @.sTGP
SET @.VARR1 = ' FROM PAMLMSTCNS WHERE AMLCBANKNU='
+ CONVERT(VARCHAR(5),@.i_AMLCBANKNU) + ' AND (' + SUBSTRING (@.sQTY, 1, LEN(@.sQTY)-1)
+ ' > 0 OR ' + SUBSTRING (@.sCSH, 1, LEN(@.sCSH)-1)
+ ' > 0 OR ' + SUBSTRING (@.sNCH, 1, LEN(@.sNCH)-1)
+ ' > 0 OR ' + SUBSTRING (@.sTOT, 1, LEN(@.sTOT)-1)
+ ' > 0 OR ' + SUBSTRING (@.sCHB, 1, LEN(@.sCHB)-1)
+ ' > 0 OR ' + @.sTGP + ' > 0)'

--PREPARE sSQLSTM FROM @.sSTM
-- DECLARE C1 CURSOR FOR @.sSTM

IF (@.i_PERIOD <> 5)
BEGIN
SELECT @.v_contador= COUNT(*)
FROM PAMLDETCNS
WHERE AMLCBANKNU = @.nBKN
AND AMLCCONSOL = @.i_PERIOD
END

IF (@.v_contador<>0)
BEGIN
DELETE FROM PAMLDETCNS
WHERE AMLCBANKNU = @.nBKN
AND AMLCCONSOL = @.i_PERIOD
END

SET @.VARC=''
SET @.VARC= @.VARC + ' DECLARE @.nBKN1 NUMERIC(3,0) DECLARE @.nAPP1 NUMERIC(2,0) DECLARE @.nACT1 NUMERIC(1,0) DECLARE @.nACC1 NUMERIC(12,0) DECLARE @.nQTY1 NUMERIC(9,0) '
SET @.VARC= @.VARC + ' DECLARE @.nGRP1 NUMERIC(2,0) DECLARE @.nCSH1 NUMERIC(12,2) DECLARE @.nNCH1 NUMERIC(12,2) DECLARE @.nTOT1 NUMERIC(12,2) DECLARE @.nCHB1 NUMERIC(12,2) '
SET @.VARC= @.VARC + ' DECLARE @.nTGP1 NUMERIC(12,2) DECLARE @.i_PERIOD1 INT '
SET @.VARC= @.VARC + ' SET @.nBKN1 =' + CONVERT(CHAR(15),@.i_AMLCBANKNU )
SET @.VARC= @.VARC + ' SET @.i_PERIOD1 =' + CONVERT(CHAR(15),@.i_PERIOD )
SET @.VARC= @.VARC + ' SET @.nAPP1 =0'
SET @.VARC= @.VARC + ' SET @.nACT1 =0'
SET @.VARC= @.VARC + ' SET @.nACC1 =0'
SET @.VARC= @.VARC + ' SET @.nQTY1 =0'
SET @.VARC= @.VARC + ' SET @.nGRP1 =0'
SET @.VARC= @.VARC + ' SET @.nCSH1 =0'
SET @.VARC= @.VARC + ' SET @.nNCH1 =0'
SET @.VARC= @.VARC + ' SET @.nTOT1 =0'
SET @.VARC= @.VARC + ' SET @.nCHB1 =0'
SET @.VARC= @.VARC + ' SET @.nTGP1 =0'
SET @.VARC = @.VARC + ' DECLARE C10 CURSOR FOR '

SET @.VARP = @.VARP + ' OPEN C10 FETCH NEXT FROM C10 '
SET @.VARP = @.VARP + ' INTO @.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1, @.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1'
SET @.VARP = @.VARP + ' WHILE @.@.FETCH_STATUS = 0'
SET @.VARP = @.VARP + ' BEGIN'
SET @.VARP = @.VARP + ' INSERT INTO PAMLDETCNS'
SET @.VARP = @.VARP + ' (AMLCBANKNU,AMLCAPPCOD,AMLCACCTYP,AMLCACCOUN,AMLCGRUTRA,AMLCCONSOL,AMLDPERIFR,AMLDPERITO,AMLNQTYTRC,AMLNCHSTRC,AMLNNCHTRC,AMLNTOTTRC,AMLNCHBTRC, AMLTOTGRPC)'
SET @.VARP = @.VARP + ' VALUES '
SET @.VARP = @.VARP + ' (@.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1,@.i_PERIOD1,' +CHAR(39)+CONVERT(CHAR(10),@.i_AMLDPERIFR,101)+CHAR(39)+','+CHAR(39)+CONVERT(CHAR(10),@.i_AMLDPERITO,101)+CHAR(39)+',@.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1)'
SET @.VARP = @.VARP + ' FETCH NEXT FROM C10 '
SET @.VARP = @.VARP + ' INTO @.nBKN1,@.nAPP1,@.nACT1,@.nACC1,@.nGRP1, @.nQTY1,@.nCSH1,@.nNCH1,@.nTOT1,@.nCHB1,@.nTGP1'
SET @.VARP = @.VARP + ' END '
SET @.VARP = @.VARP + ' CLOSE C10 '
SET @.VARP = @.VARP + ' DEALLOCATE C10 '

print LEN(@.sSTM)
print @.VARC + @.VARR
print @.VARR1 + @.VARP
--EXEC sp_executesql @.VARC

EXEC('EXEC sp_executesql ' + @.VARC + @.VARR + @.VARR1 + @.VARP )

END
as you can see my store open a cursor inside of the query.... any suggestion?

thks

Wednesday, March 21, 2012

Help with Reindexing all tables in a Database?

Hello,
I was provided this script:
DECLARE @.TableName varchar(255)
DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = 'base table'
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @.TableName
WHILE @.@.FETCH_STATUS = 0
BEGIN
DBCC DBREINDEX(@.TableName,' ',90)
FETCH NEXT FROM TableCursor INTO @.TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
However, I am not sure of what the variables:
table_name
information_schema.tables
base table
are. And if I do not have to give them values - how does the script
know what they are?
Am I required to fill them in? And if so with what data.
I do have access to the DB and can see all the table names and have
logged in as the database owner.
I tried running it as it and got this:
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
DBCC execution completed. If DBCC printed error messages, contact your
system administrator.
Server: Msg 2501, Level 16, State 1, Line 12
Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
Any suggestions for the forced into place back up dba?
Thanks,
TmuldMaybe you're simply running this script in the wrong database?
Make sure you've selected the correct database in the database selection
list in the menu bar before running the script.
It might even help if you placed a USE command at the top of the script so
that it ensures the correct DB is being used when the script is run, eg:
USE [yourdbname]
DECALRE @.TableName...
Regards,
Greg Linwood
SQL Server MVP
http://blogs.sqlserver.org.au/blogs/greg_linwood
"Tmuldoon" <tmuldoon@.spliced.com> wrote in message
news:1176418044.844493.175160@.y80g2000hsf.googlegroups.com...
> Hello,
> I was provided this script:
> DECLARE @.TableName varchar(255)
> DECLARE TableCursor CURSOR FOR
> SELECT table_name FROM information_schema.tables
> WHERE table_type = 'base table'
> OPEN TableCursor
> FETCH NEXT FROM TableCursor INTO @.TableName
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> DBCC DBREINDEX(@.TableName,' ',90)
> FETCH NEXT FROM TableCursor INTO @.TableName
> END
> CLOSE TableCursor
> DEALLOCATE TableCursor
> However, I am not sure of what the variables:
> table_name
> information_schema.tables
> base table
> are. And if I do not have to give them values - how does the script
> know what they are?
> Am I required to fill them in? And if so with what data.
> I do have access to the DB and can see all the table names and have
> logged in as the database owner.
> I tried running it as it and got this:
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> DBCC execution completed. If DBCC printed error messages, contact your
> system administrator.
> Server: Msg 2501, Level 16, State 1, Line 12
> Could not find a table or object named 'FIRSTNAME'. Check sysobjects.
> Any suggestions for the forced into place back up dba?
> Thanks,
> Tmuld
>sql

Help with Reindexing all tables in a Database?

Monday, March 19, 2012

Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id
"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:

>I have the following nonindexable query due to the "<>" operater. Column "i
d"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index see
k
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:

> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestat
s)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is stil
l
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:[vbcol=seagreen]
>If you have a clustered index on the "id" column, the index will get used i
n
>the search, but it will be an index scan rather than a index seek(which wil
l
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index se
ek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you hav
e
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause
.
>HTH
>RA
>
>[quoted text clipped - 22 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via droptable.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could
be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatest
ats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause
:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is st
ill
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would
be
> appreciated.
> Rocky A wrote:
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Help with query rewrite

I have the following nonindexable query due to the "<>" operater. Column "id"
in the following scenario is a clustered index.
DECLARE @.tid
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
Is there a way to rewrite such a search condition so as to make it an
indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1How large is the table? How much of the table is equal to @.tid? Is
t.id unique?
If the test is going to eliminate only a small percentage of the table
then a table scan is the fastest way to get through it. In that case
the most you can hope for is that perhaps clustering on t.id will make
a small difference.
If the test will eliminate a major percentage of the table then a
clustering on t.id would probably help.
Roy Harvey
Beacon Falls, CT
On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
>I have the following nonindexable query due to the "<>" operater. Column "id"
>in the following scenario is a clustered index.
>DECLARE @.tid
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>
>Is there a way to rewrite such a search condition so as to make it an
>indexexable search condition?|||If you have a clustered index on the "id" column, the index will get used in
the search, but it will be an index scan rather than a index seek(which will
be in case of =), for the simple reason that you are not trying to find one
value, you are trying to eleminate a value, so it has to compare it against
every value in the index. So I am assuming you are looking for an index seek
rather than a scan,
You can try doing this
DECLARE @.tid int
SET @.tid = 1000
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
I am not sure if you can make this assumption that "id" will always be > 0,
this actually runs an index seek on the table. The plan improves if you have
this inside a stored proc, as the query plan gets cached.
As Roy pointed out this can be a very expensive query without anything else
in your where clause depending on the size of the data. It almost took 31
seconds for me to run this on 11 Million records. If I was you I would look
at changing the query and including some more filtering in the where clause.
HTH
RA
"Roy Harvey" wrote:
> How large is the table? How much of the table is equal to @.tid? Is
> t.id unique?
> If the test is going to eliminate only a small percentage of the table
> then a table scan is the fastest way to get through it. In that case
> the most you can hope for is that perhaps clustering on t.id will make
> a small difference.
> If the test will eliminate a major percentage of the table then a
> clustering on t.id would probably help.
> Roy Harvey
> Beacon Falls, CT
> On Wed, 03 May 2006 23:04:56 GMT, "cbrichards" <u3288@.uwe> wrote:
> >I have the following nonindexable query due to the "<>" operater. Column "id"
> >in the following scenario is a clustered index.
> >
> >DECLARE @.tid
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >
> >
> >Is there a way to rewrite such a search condition so as to make it an
> >indexexable search condition?
>|||Thanks Rocky.
The "id" column is a nonclustered composite index with another column name
"col_k". The index was created with in this order (id, col_k).
Column "id" is an identity column and column "col_k" is not very unique.
There are approximately 15,000 records in the table.
Since Column "id" is first in the composite index, it seems like it could be
used in the revised query you wrote, but perhaps the optimizer believes a
scan is still faster than using the query (and yes, I have run sp_updatestats)
.
I further rewrote the query to give it an extra filter in the WHERE clause:
DECLARE @.tid int
DECLARE @.colk int
SET @.tid = 1000
SET @.colk = 5
SELECT t.id
FROM table t
WHERE t.id <> @.tid
and t.id>0
and t.col_k = @.colk
However, my logical reads have not improved from the original and it is still
performing a Clustered Index Scan (using the clustered index which is a
datetime field), which to me in this case, is in essence a table scan.
Any further ideas or suggestions to have it use the composite index would be
appreciated.
Rocky A wrote:
>If you have a clustered index on the "id" column, the index will get used in
>the search, but it will be an index scan rather than a index seek(which will
>be in case of =), for the simple reason that you are not trying to find one
>value, you are trying to eleminate a value, so it has to compare it against
>every value in the index. So I am assuming you are looking for an index seek
>rather than a scan,
>You can try doing this
>DECLARE @.tid int
>SET @.tid = 1000
>SELECT t.id
>FROM table t
>WHERE t.id <> @.tid
>and t.id>0
>I am not sure if you can make this assumption that "id" will always be > 0,
>this actually runs an index seek on the table. The plan improves if you have
>this inside a stored proc, as the query plan gets cached.
>As Roy pointed out this can be a very expensive query without anything else
>in your where clause depending on the size of the data. It almost took 31
>seconds for me to run this on 11 Million records. If I was you I would look
>at changing the query and including some more filtering in the where clause.
>HTH
>RA
>> How large is the table? How much of the table is equal to @.tid? Is
>> t.id unique?
>[quoted text clipped - 22 lines]
>> >Is there a way to rewrite such a search condition so as to make it an
>> >indexexable search condition?
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||RA,
Is this your real query, or a simplified version?
If id is the only column you are selecting, then one would expect that
SQL-Server would scan the smallest index that contains the id column.
You did not post DDL, so we cannot check this.
If you are selecting other columns than the ones in the nonclustered
index and clustered index, then the nonclustered index will (most
likely) not be used, because that would require bookmark lookups for too
many rows. In that case, a clustered index scan would simply be faster.
Gert-Jan
"cbrichards via SQLMonster.com" wrote:
> Thanks Rocky.
> The "id" column is a nonclustered composite index with another column name
> "col_k". The index was created with in this order (id, col_k).
> Column "id" is an identity column and column "col_k" is not very unique.
> There are approximately 15,000 records in the table.
> Since Column "id" is first in the composite index, it seems like it could be
> used in the revised query you wrote, but perhaps the optimizer believes a
> scan is still faster than using the query (and yes, I have run sp_updatestats)
> .
> I further rewrote the query to give it an extra filter in the WHERE clause:
> DECLARE @.tid int
> DECLARE @.colk int
> SET @.tid = 1000
> SET @.colk = 5
> SELECT t.id
> FROM table t
> WHERE t.id <> @.tid
> and t.id>0
> and t.col_k = @.colk
> However, my logical reads have not improved from the original and it is still
> performing a Clustered Index Scan (using the clustered index which is a
> datetime field), which to me in this case, is in essence a table scan.
> Any further ideas or suggestions to have it use the composite index would be
> appreciated.
> Rocky A wrote:
> >If you have a clustered index on the "id" column, the index will get used in
> >the search, but it will be an index scan rather than a index seek(which will
> >be in case of =), for the simple reason that you are not trying to find one
> >value, you are trying to eleminate a value, so it has to compare it against
> >every value in the index. So I am assuming you are looking for an index seek
> >rather than a scan,
> >
> >You can try doing this
> >
> >DECLARE @.tid int
> >SET @.tid = 1000
> >
> >SELECT t.id
> >FROM table t
> >WHERE t.id <> @.tid
> >and t.id>0
> >
> >I am not sure if you can make this assumption that "id" will always be > 0,
> >this actually runs an index seek on the table. The plan improves if you have
> >this inside a stored proc, as the query plan gets cached.
> >
> >As Roy pointed out this can be a very expensive query without anything else
> >in your where clause depending on the size of the data. It almost took 31
> >seconds for me to run this on 11 Million records. If I was you I would look
> >at changing the query and including some more filtering in the where clause.
> >
> >HTH
> >RA
> >
> >> How large is the table? How much of the table is equal to @.tid? Is
> >> t.id unique?
> >[quoted text clipped - 22 lines]
> >> >Is there a way to rewrite such a search condition so as to make it an
> >> >indexexable search condition?
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200605/1|||given the specific example, i would expect the engine to use any index
it can.
however, sending the data across the wire will be the bottleneck, so no
matter what it probably take as long as it takes to send the data.

Sunday, February 19, 2012

Help with inserting a new record into database - error Must declare the scalar variable "@

Hi,

Can anybody help me with this, I've got a simple program to add a new record to a table (2 items ID - Integer and Program - String) that matches all examples I can find, but when I run it I get the error :

Must declare the scalar variable "@.BookMarkArrayA".

when it reaches the .insert command, I've tried using a local variable temp in place of the array element and.ToString , but still get the same error

This is the code :

PublicSub NewCustomer()

Dim tempAsString =" "

Dim IDAsInteger = 1

'Restore the array from the view state

BookMarkArrayA =Me.ViewState("BookMarkArrayA")

temp = BookMarkArrayA(6)

Dim CustomerAs SqlDataSource =New SqlDataSource()

Customer.ConnectionString = ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString()

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

Customer.Insert()

EndSub

Cheers

Ken

I'm not sure where you got the (6) syntax from?

Try this. Change these 3 lines:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA(6))"

Customer.InsertParameters.Add ("ID", ID)

Customer.InsertParameters.Add ("Program",@.BookMarkArrayA(6))

to this and you should have better luck:
Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.BookMarkArrayA)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Integer).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value =@.BookMarkArrayA)

|||

Terri,

Thanks for the reply, it didn't compile as the SqlDbType wasn't recognised.

The origional error was @.BookMarkArray was an undeclared scalar variable, which suggests to me that this part of the command requires a pointer to the actual variable BookMarkArray denoted by putting the @. symbol first.

The (6) syntak by the way, was the element of the array I wanted to load into the DB.

The code fits with other examples I've looked up, I'm proberbly missing something simple.

Ken

|||Boy did I screw up the code. Embarrassed [:$]

I am sorry about that. Here's what it *should* be:

Customer.InsertCommand ="INSERT INTO [Table1] ([ID],[Program]) VALUES (@.ID, @.Program)"

Customer.InsertParameters.Add ("@.ID", SqlDbType.Int).Value = ID

Customer.InsertParameters.Add ("@.Program", SqlDbType.VarChar,6).Value = BookMarkArray(6)

|||

Terri,

I still get the error that SqlDbType is not declared, am i missing inheriting a library or some thing

Ken

|||Well, try qualifying it by adding the namespace in front and see if that takes care of it.

SqlClient.SqlDbType.Int|||

Terri,

I get the error SqlClient not declared, so I assume that I'm not inheriting something.

I tried the following code which worked, but I can't find a method to change the String value 'Test Program' into a parameter:

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd

.CommandType = System.Data.CommandType.Text

.CommandText =

"INSERT Into Customer (Program) VALUES ('Test Program')"

.Connection = sqlConnection1

EndWith

sqlConnection1.Open()

cmd.ExecuteNonQuery()

sqlConnection1.Close()

Ken

|||Try that code block like this (updates in pink):

Dim sqlConnection1AsNew System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("CustomerConnectionString").ToString())Dim cmdAsNew System.Data.SqlClient.SqlCommandWith cmd
.CommandType = System.Data.CommandType.Text
.CommandText = "INSERT Into Customer (Program) VALUES (@.Program)"
.Connection = sqlConnection1
EndWith
cmd.Parameters.Add("@.Program", System.Data.SqlDbType.VarChar, 99).Value = "Test Program"
sqlConnection1.Open()
cmd.ExecuteNonQuery()
sqlConnection1.Close()

|||

Terri,

Thanks, that worked, althrough I'd tried variations of adding parameters before without success.

My concern is that I don't seem to be able to see the namespace System.Data.SQLClient and when you look at the methods, it says you must reference this namespace but don't tell you how.

Ken

|||

KenWalker:
My concern is that I don't seem to be able to see the namespace System.Data.SQLClient

If you are using code inline (ie, not a separate .vb file), put this at the top of the page, right below the @.Page directive:

<%@. Import Namespace="System.Configuration" %>

If you are using code beside/behind, put this at the very top of your code:

Imports System.Data.Client

|||

I'm lazy, so I put it in web.config under the system.web section:

<pagestheme="default">

<addnamespace="System.Data"/>

<addnamespace="System.Data.SqlClient"/>

<addnamespace="System.Configuration.ConfigurationManager"/>

</namespaces>

</pages>

|||

Thanks Terry, I have solved the problem using -Dim cmdAsNew System.Data.SqlClient.SqlCommand and then adding the parameters that way.

Ken

Monday, March 26, 2012

help with sp_executesql

Wednesday, March 21, 2012

Help with Reindexing all tables in a Database?

Help with Reindexing all tables in a Database?

Monday, March 19, 2012

Help with query rewrite

Help with query rewrite

Sunday, February 19, 2012

Help with inserting a new record into database - error Must declare the scalar variable "@

Max Query

Blog Archive

About Me