sql server codes: query

Showing posts with label query. Show all posts

Friday, March 30, 2012

help out of control query

/** TABLE
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SitesID] [numeric](18, 0) NOT NULL ,
[CompanyID] [numeric](18, 0) NOT NULL ,
[Via] [numeric](18, 0) NOT NULL ,
[Deleted] [bit] NOT NULL ,
[Void] [bit] NOT NULL ,
[CheckSigla] [bit] NOT NULL ,
[UserIDIN] [numeric](18, 0) NOT NULL ,
[UserIDMOD] [numeric](18, 0) NOT NULL ,
[DataIN] [datetime] NOT NULL ,
[DataMod] [datetime] NOT NULL ,
[VendorID] [numeric](18, 0) NOT NULL ,
[VendorIDD] [numeric](18, 0) NOT NULL ,
[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InvoiceDate] [datetime] NOT NULL ,
[TotImponibile] [float] NOT NULL ,
[TotIva] [float] NOT NULL ,
[TotInvoice] [float] NOT NULL ,
[TotImponibileF] [float] NOT NULL ,
[TotIvaF] [float] NOT NULL ,
[TotInvoiceF] [float] NOT NULL ,
[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InvoiceToCheck] [float] NOT NULL ,
[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[VendorType] [numeric](18, 0) NOT NULL ,
[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Checked] [bit] NOT NULL ,
[NotaCredito] [bit] NOT NULL ,
[AutoFattura] [bit] NOT NULL ,
[WhoDeleted] [numeric](18, 0) NOT NULL ,
[DataDeleted] [datetime] NOT NULL ,
[PaymentTermsID] [numeric](18, 0) NOT NULL
/**
from program is
"execute ODM1 iID "
iID is the numeric for the record ID and goes by adoQuery
Thanks,
Paolol
|||Hi
Well ,looks strange.Do you have any triggers defined on the table?
create table #test
(
col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
col2 char(1)
)
insert into #test (col2) values ('a')
insert into #test (col2)values ('b')
insert into #test (col2) values ('c')
select * from #test
declare @.p numeric
set @.p=1
update #test set col2 ='g' where col1=@.p.
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant ha scritto:
> Hi NP

> /** TABLE
> [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SitesID] [numeric](18, 0) NOT NULL ,
> [CompanyID] [numeric](18, 0) NOT NULL ,
> [Via] [numeric](18, 0) NOT NULL ,
> [Deleted] [bit] NOT NULL ,
> [Void] [bit] NOT NULL ,
> [CheckSigla] [bit] NOT NULL ,
> [UserIDIN] [numeric](18, 0) NOT NULL ,
> [UserIDMOD] [numeric](18, 0) NOT NULL ,
> [DataIN] [datetime] NOT NULL ,
> [DataMod] [datetime] NOT NULL ,
> [VendorID] [numeric](18, 0) NOT NULL ,
> [VendorIDD] [numeric](18, 0) NOT NULL ,
> [InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [InvoiceDate] [datetime] NOT NULL ,
> [TotImponibile] [float] NOT NULL ,
> [TotIva] [float] NOT NULL ,
> [TotInvoice] [float] NOT NULL ,
> [TotImponibileF] [float] NOT NULL ,
> [TotIvaF] [float] NOT NULL ,
> [TotInvoiceF] [float] NOT NULL ,
> [CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [InvoiceToCheck] [float] NOT NULL ,
> [VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [VendorType] [numeric](18, 0) NOT NULL ,
> [TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Checked] [bit] NOT NULL ,
> [NotaCredito] [bit] NOT NULL ,
> [AutoFattura] [bit] NOT NULL ,
> [WhoDeleted] [numeric](18, 0) NOT NULL ,
> [DataDeleted] [datetime] NOT NULL ,
> [PaymentTermsID] [numeric](18, 0) NOT NULL
> /**
> from program is
> "execute ODM1 iID "
> iID is the numeric for the record ID and goes by adoQuery
> Thanks,
> Paolol
|||Hi Uri,
yes very strange

No no trigger at all, and this procedure is not called by any other, is
a stand alone procedure and the most strange think is the DataDeleted
that is exactly the same for all deleted record !!! up to the millisecond !!
Uri Dimant ha scritto:
> Hi
> Well ,looks strange.Do you have any triggers defined on the table?
>
> create table #test
> (
> col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> col2 char(1)
> )
> insert into #test (col2) values ('a')
> insert into #test (col2)values ('b')
> insert into #test (col2) values ('c')
> select * from #test
> declare @.p numeric
> set @.p=1
> update #test set col2 ='g' where col1=@.p.
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
>
>
|||My guess is that someone executed some other UPDATE or stored procedure to perform this action...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:%23b1zOXWAGHA.3064@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Uri,
> yes very strange

> No no trigger at all, and this procedure is not called by any other, is a stand alone procedure
> and the most strange think is the DataDeleted that is exactly the same for all deleted record !!!
> up to the millisecond !!
>
> Uri Dimant ha scritto:

help out of control query

Hi,
I jsut had a big problem, a table of 15000 was updated on 10000 for a
bit changed to true and all the record show the same datetime for the
change, the only query how can do this change is this :
/**
CREATE PROCEDURE OdM1
@.InvoicesPassID [numeric]
AS
update InvoicesPassive
set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
where ID=@.InvoicesPassID
/**
How this query can do that ?
Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
Thanks for any suggestion.
PaololOnly the affected rows should reflect the new datetime.
jens Suessmeyer.|||Hi
Can you show us a table structure and how do you assign a parameter to the
SP?
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
> changed to true and all the record show the same datetime for the change,
> the only query how can do this change is this :
> /**
> CREATE PROCEDURE OdM1
> @.InvoicesPassID [numeric]
> AS
> update InvoicesPassive
> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
> where ID=@.InvoicesPassID
> /**
> How this query can do that ?
> Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
> Thanks for any suggestion.
> Paolol|||Uri Dimant ha scritto:
> Hi
> Can you show us a table structure and how do you assign a parameter to the
> SP?
>
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>>Hi,
>>I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
>>changed to true and all the record show the same datetime for the change,
>>the only query how can do this change is this :
>>/**
>>CREATE PROCEDURE OdM1
>>@.InvoicesPassID [numeric]
>>AS
>> update InvoicesPassive
>> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
>> where ID=@.InvoicesPassID
>>/**
>>How this query can do that ?
>>Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
>>Thanks for any suggestion.
>>Paolol
>
>
Hi NP :)
/** TABLE
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SitesID] [numeric](18, 0) NOT NULL ,
[CompanyID] [numeric](18, 0) NOT NULL ,
[Via] [numeric](18, 0) NOT NULL ,
[Deleted] [bit] NOT NULL ,
[Void] [bit] NOT NULL ,
[CheckSigla] [bit] NOT NULL ,
[UserIDIN] [numeric](18, 0) NOT NULL ,
[UserIDMOD] [numeric](18, 0) NOT NULL ,
[DataIN] [datetime] NOT NULL ,
[DataMod] [datetime] NOT NULL ,
[VendorID] [numeric](18, 0) NOT NULL ,
[VendorIDD] [numeric](18, 0) NOT NULL ,
[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InvoiceDate] [datetime] NOT NULL ,
[TotImponibile] [float] NOT NULL ,
[TotIva] [float] NOT NULL ,
[TotInvoice] [float] NOT NULL ,
[TotImponibileF] [float] NOT NULL ,
[TotIvaF] [float] NOT NULL ,
[TotInvoiceF] [float] NOT NULL ,
[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[InvoiceToCheck] [float] NOT NULL ,
[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[VendorType] [numeric](18, 0) NOT NULL ,
[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Checked] [bit] NOT NULL ,
[NotaCredito] [bit] NOT NULL ,
[AutoFattura] [bit] NOT NULL ,
[WhoDeleted] [numeric](18, 0) NOT NULL ,
[DataDeleted] [datetime] NOT NULL ,
[PaymentTermsID] [numeric](18, 0) NOT NULL
/**
from program is
"execute ODM1 iID "
iID is the numeric for the record ID and goes by adoQuery
Thanks,
Paolol|||Hi
Well ,looks strange.Do you have any triggers defined on the table?
create table #test
(
col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
col2 char(1)
)
insert into #test (col2) values ('a')
insert into #test (col2)values ('b')
insert into #test (col2) values ('c')
select * from #test
declare @.p numeric
set @.p=1
update #test set col2 ='g' where col1=@.p.
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant ha scritto:
>> Hi
>> Can you show us a table structure and how do you assign a parameter to
>> the SP?
>>
>>
>> "paolol" <paolol@._nospamma_salsan.net> wrote in message
>> news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>>Hi,
>>I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
>>changed to true and all the record show the same datetime for the change,
>>the only query how can do this change is this :
>>/**
>>CREATE PROCEDURE OdM1
>>@.InvoicesPassID [numeric]
>>AS
>> update InvoicesPassive
>> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
>> where ID=@.InvoicesPassID
>>/**
>>How this query can do that ?
>>Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
>>Thanks for any suggestion.
>>Paolol
>>
> Hi NP :)
> /** TABLE
> [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SitesID] [numeric](18, 0) NOT NULL ,
> [CompanyID] [numeric](18, 0) NOT NULL ,
> [Via] [numeric](18, 0) NOT NULL ,
> [Deleted] [bit] NOT NULL ,
> [Void] [bit] NOT NULL ,
> [CheckSigla] [bit] NOT NULL ,
> [UserIDIN] [numeric](18, 0) NOT NULL ,
> [UserIDMOD] [numeric](18, 0) NOT NULL ,
> [DataIN] [datetime] NOT NULL ,
> [DataMod] [datetime] NOT NULL ,
> [VendorID] [numeric](18, 0) NOT NULL ,
> [VendorIDD] [numeric](18, 0) NOT NULL ,
> [InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [InvoiceDate] [datetime] NOT NULL ,
> [TotImponibile] [float] NOT NULL ,
> [TotIva] [float] NOT NULL ,
> [TotInvoice] [float] NOT NULL ,
> [TotImponibileF] [float] NOT NULL ,
> [TotIvaF] [float] NOT NULL ,
> [TotInvoiceF] [float] NOT NULL ,
> [CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [InvoiceToCheck] [float] NOT NULL ,
> [VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ,
> [VendorType] [numeric](18, 0) NOT NULL ,
> [TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
> [Checked] [bit] NOT NULL ,
> [NotaCredito] [bit] NOT NULL ,
> [AutoFattura] [bit] NOT NULL ,
> [WhoDeleted] [numeric](18, 0) NOT NULL ,
> [DataDeleted] [datetime] NOT NULL ,
> [PaymentTermsID] [numeric](18, 0) NOT NULL
> /**
> from program is
> "execute ODM1 iID "
> iID is the numeric for the record ID and goes by adoQuery
> Thanks,
> Paolol|||Hi Uri,
yes very strange :(
No no trigger at all, and this procedure is not called by any other, is
a stand alone procedure and the most strange think is the DataDeleted
that is exactly the same for all deleted record !!! up to the millisecond !!
Uri Dimant ha scritto:
> Hi
> Well ,looks strange.Do you have any triggers defined on the table?
>
> create table #test
> (
> col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> col2 char(1)
> )
> insert into #test (col2) values ('a')
> insert into #test (col2)values ('b')
> insert into #test (col2) values ('c')
> select * from #test
> declare @.p numeric
> set @.p=1
> update #test set col2 ='g' where col1=@.p.
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
>>Uri Dimant ha scritto:
>>Hi
>>Can you show us a table structure and how do you assign a parameter to
>>the SP?
>>
>>
>>"paolol" <paolol@._nospamma_salsan.net> wrote in message
>>news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>>
>>Hi,
>>I jsut had a big problem, a table of 15000 was updated on 10000 for a bit
>>changed to true and all the record show the same datetime for the change,
>>the only query how can do this change is this :
>>/**
>>CREATE PROCEDURE OdM1
>>@.InvoicesPassID [numeric]
>>AS
>>update InvoicesPassive
>> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
>> where ID=@.InvoicesPassID
>>/**
>>How this query can do that ?
>>Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
>>Thanks for any suggestion.
>>Paolol
>>
>>Hi NP :)
>>/** TABLE
>>[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
>>[SitesID] [numeric](18, 0) NOT NULL ,
>>[CompanyID] [numeric](18, 0) NOT NULL ,
>>[Via] [numeric](18, 0) NOT NULL ,
>>[Deleted] [bit] NOT NULL ,
>>[Void] [bit] NOT NULL ,
>>[CheckSigla] [bit] NOT NULL ,
>>[UserIDIN] [numeric](18, 0) NOT NULL ,
>>[UserIDMOD] [numeric](18, 0) NOT NULL ,
>>[DataIN] [datetime] NOT NULL ,
>>[DataMod] [datetime] NOT NULL ,
>>[VendorID] [numeric](18, 0) NOT NULL ,
>>[VendorIDD] [numeric](18, 0) NOT NULL ,
>>[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT
>>NULL ,
>>[InvoiceDate] [datetime] NOT NULL ,
>>[TotImponibile] [float] NOT NULL ,
>>[TotIva] [float] NOT NULL ,
>>[TotInvoice] [float] NOT NULL ,
>>[TotImponibileF] [float] NOT NULL ,
>>[TotIvaF] [float] NOT NULL ,
>>[TotInvoiceF] [float] NOT NULL ,
>>[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>>,
>>[InvoiceToCheck] [float] NOT NULL ,
>>[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
>>,
>>[VendorType] [numeric](18, 0) NOT NULL ,
>>[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[Checked] [bit] NOT NULL ,
>>[NotaCredito] [bit] NOT NULL ,
>>[AutoFattura] [bit] NOT NULL ,
>>[WhoDeleted] [numeric](18, 0) NOT NULL ,
>>[DataDeleted] [datetime] NOT NULL ,
>>[PaymentTermsID] [numeric](18, 0) NOT NULL
>>/**
>>from program is
>>"execute ODM1 iID "
>>iID is the numeric for the record ID and goes by adoQuery
>>Thanks,
>>Paolol
>
>|||My guess is that someone executed some other UPDATE or stored procedure to perform this action...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:%23b1zOXWAGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Hi Uri,
> yes very strange :(
> No no trigger at all, and this procedure is not called by any other, is a stand alone procedure
> and the most strange think is the DataDeleted that is exactly the same for all deleted record !!!
> up to the millisecond !!
>
> Uri Dimant ha scritto:
>> Hi
>> Well ,looks strange.Do you have any triggers defined on the table?
>>
>> create table #test
>> (
>> col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
>> col2 char(1)
>> )
>> insert into #test (col2) values ('a')
>> insert into #test (col2)values ('b')
>> insert into #test (col2) values ('c')
>> select * from #test
>> declare @.p numeric
>> set @.p=1
>> update #test set col2 ='g' where col1=@.p.
>>
>> "paolol" <paolol@._nospamma_salsan.net> wrote in message
>> news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
>>Uri Dimant ha scritto:
>>Hi
>>Can you show us a table structure and how do you assign a parameter to the SP?
>>
>>
>>"paolol" <paolol@._nospamma_salsan.net> wrote in message
>>news:utzQjbVAGHA.1676@.TK2MSFTNGP09.phx.gbl...
>>
>>Hi,
>>I jsut had a big problem, a table of 15000 was updated on 10000 for a bit changed to true and
>>all the record show the same datetime for the change, the only query how can do this change is
>>this :
>>/**
>>CREATE PROCEDURE OdM1
>>@.InvoicesPassID [numeric]
>>AS
>>update InvoicesPassive
>> set Deleted=1 , WhoDeleted=@.UserID , DataDeleted=getdate()
>> where ID=@.InvoicesPassID
>>/**
>>How this query can do that ?
>>Can a numeric have a jolly number ? and work as a LIKE 'x%' ?
>>Thanks for any suggestion.
>>Paolol
>>
>>Hi NP :)
>>/** TABLE
>>[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
>>[SitesID] [numeric](18, 0) NOT NULL ,
>>[CompanyID] [numeric](18, 0) NOT NULL ,
>>[Via] [numeric](18, 0) NOT NULL ,
>>[Deleted] [bit] NOT NULL ,
>>[Void] [bit] NOT NULL ,
>>[CheckSigla] [bit] NOT NULL ,
>>[UserIDIN] [numeric](18, 0) NOT NULL ,
>>[UserIDMOD] [numeric](18, 0) NOT NULL ,
>>[DataIN] [datetime] NOT NULL ,
>>[DataMod] [datetime] NOT NULL ,
>>[VendorID] [numeric](18, 0) NOT NULL ,
>>[VendorIDD] [numeric](18, 0) NOT NULL ,
>>[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[InvoiceDate] [datetime] NOT NULL ,
>>[TotImponibile] [float] NOT NULL ,
>>[TotIva] [float] NOT NULL ,
>>[TotInvoice] [float] NOT NULL ,
>>[TotImponibileF] [float] NOT NULL ,
>>[TotIvaF] [float] NOT NULL ,
>>[TotInvoiceF] [float] NOT NULL ,
>>[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[InvoiceToCheck] [float] NOT NULL ,
>>[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[VendorType] [numeric](18, 0) NOT NULL ,
>>[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
>>[Checked] [bit] NOT NULL ,
>>[NotaCredito] [bit] NOT NULL ,
>>[AutoFattura] [bit] NOT NULL ,
>>[WhoDeleted] [numeric](18, 0) NOT NULL ,
>>[DataDeleted] [datetime] NOT NULL ,
>>[PaymentTermsID] [numeric](18, 0) NOT NULL
>>/**
>>from program is
>>"execute ODM1 iID "
>>iID is the numeric for the record ID and goes by adoQuery
>>Thanks,
>>Paolol
>>

help out of control query

/** TABLE
[ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[SitesID] [numeric](18, 0) NOT NULL ,
[CompanyID] [numeric](18, 0) NOT NULL ,
[Via] [numeric](18, 0) NOT NULL ,
[Deleted] [bit] NOT NULL ,
[Void] [bit] NOT NULL ,
[CheckSigla] [bit] NOT NULL ,
[UserIDIN] [numeric](18, 0) NOT NULL ,
[UserIDMOD] [numeric](18, 0) NOT NULL ,
[DataIN] [datetime] NOT NULL ,
[DataMod] [datetime] NOT NULL ,
[VendorID] [numeric](18, 0) NOT NULL ,
[VendorIDD] [numeric](18, 0) NOT NULL ,
[InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS N
OT
NULL ,
[InvoiceDate] [datetime] NOT NULL ,
[TotImponibile] [float] NOT NULL ,
[TotIva] [float] NOT NULL ,
[TotInvoice] [float] NOT NULL ,
[TotImponibileF] [float] NOT NULL ,
[TotIvaF] [float] NOT NULL ,
[TotInvoiceF] [float] NOT NULL ,
[CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
[InvoiceToCheck] [float] NOT NULL ,
[VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NO
T
NULL ,
[VendorType] [numeric](18, 0) NOT NULL ,
[TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NU
LL ,
[Checked] [bit] NOT NULL ,
[NotaCredito] [bit] NOT NULL ,
[AutoFattura] [bit] NOT NULL ,
[WhoDeleted] [numeric](18, 0) NOT NULL ,
[DataDeleted] [datetime] NOT NULL ,
[PaymentTermsID] [numeric](18, 0) NOT NULL
/**
from program is
"execute ODM1 iID "
iID is the numeric for the record ID and goes by adoQuery
Thanks,
Paolol|||Hi
Well ,looks strange.Do you have any triggers defined on the table?
create table #test
(
col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
col2 char(1)
)
insert into #test (col2) values ('a')
insert into #test (col2)values ('b')
insert into #test (col2) values ('c')
select * from #test
declare @.p numeric
set @.p=1
update #test set col2 ='g' where col1=@.p.
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
> Uri Dimant ha scritto:
> Hi NP

> /** TABLE
> [ID] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> [SitesID] [numeric](18, 0) NOT NULL ,
> [CompanyID] [numeric](18, 0) NOT NULL ,
> [Via] [numeric](18, 0) NOT NULL ,
> [Deleted] [bit] NOT NULL ,
> [Void] [bit] NOT NULL ,
> [CheckSigla] [bit] NOT NULL ,
> [UserIDIN] [numeric](18, 0) NOT NULL ,
> [UserIDMOD] [numeric](18, 0) NOT NULL ,
> [DataIN] [datetime] NOT NULL ,
> [DataMod] [datetime] NOT NULL ,
> [VendorID] [numeric](18, 0) NOT NULL ,
> [VendorIDD] [numeric](18, 0) NOT NULL ,
> [InvoiceNumber] [varchar] (50) COLLATE QL_Latin1_General_CP1_CI_AS
NOT
> NULL ,
> [InvoiceDate] [datetime] NOT NULL ,
> [TotImponibile] [float] NOT NULL ,
> [TotIva] [float] NOT NULL ,
> [TotInvoice] [float] NOT NULL ,
> [TotImponibileF] [float] NOT NULL ,
> [TotIvaF] [float] NOT NULL ,
> [TotInvoiceF] [float] NOT NULL ,
> [CurrInvoice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS N
OT NULL
> ,
> [InvoiceToCheck] [float] NOT NULL ,
> [VendorName] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS
NOT NULL
> ,
> [VendorType] [numeric](18, 0) NOT NULL ,
> [TaxCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
NULL ,
> [Checked] [bit] NOT NULL ,
> [NotaCredito] [bit] NOT NULL ,
> [AutoFattura] [bit] NOT NULL ,
> [WhoDeleted] [numeric](18, 0) NOT NULL ,
> [DataDeleted] [datetime] NOT NULL ,
> [PaymentTermsID] [numeric](18, 0) NOT NULL
> /**
> from program is
> "execute ODM1 iID "
> iID is the numeric for the record ID and goes by adoQuery
> Thanks,
> Paolol|||Hi Uri,
yes very strange

No no trigger at all, and this procedure is not called by any other, is
a stand alone procedure and the most strange think is the DataDeleted
that is exactly the same for all deleted record !!! up to the millisecond !!
Uri Dimant ha scritto:
> Hi
> Well ,looks strange.Do you have any triggers defined on the table?
>
> create table #test
> (
> col1 [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
> col2 char(1)
> )
> insert into #test (col2) values ('a')
> insert into #test (col2)values ('b')
> insert into #test (col2) values ('c')
> select * from #test
> declare @.p numeric
> set @.p=1
> update #test set col2 ='g' where col1=@.p.
>
> "paolol" <paolol@._nospamma_salsan.net> wrote in message
> news:Ou%23CxJWAGHA.2156@.TK2MSFTNGP11.phx.gbl...
>
>
>|||My guess is that someone executed some other UPDATE or stored procedure to p
erform this action...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"paolol" <paolol@._nospamma_salsan.net> wrote in message
news:%23b1zOXWAGHA.3064@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> Hi Uri,
> yes very strange

> No no trigger at all, and this procedure is not called by any other, is a
stand alone procedure
> and the most strange think is the DataDeleted that is exactly the same for
all deleted record !!!
> up to the millisecond !!
>
> Uri Dimant ha scritto:

Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdbOn Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.
4ax.com:

> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Roy Harvey <roy_harvey@.snet.net> wrote in
> news:k4uf935qq5jqltamrffo96mgsemsnojorh@.
4ax.com:
>
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdbOn Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:
>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:
> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:
>Roy Harvey <roy_harvey@.snet.net> wrote in
>news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:
>> If the index were clustered on the date then it certainly will help.
>> But I would not rush to cluster on the date, the choice of a proper
>> clustered index is not that simple.
>> If the date ranges are very narrow compared to the overall table then
>> a non-clustered index on the date may very well be chosen by the
>> optimizer and save time. The optimizer is going to estimate the
>> number of pages it will have to read in random order to use the index,
>> and compare that to the number of pages it will have to read to scan
>> the entire table. The smaller number wins.
>> Without more information on the table, data and query I don't know
>> what else to say.
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

Help optimizing query for large data set

Hi all... I'm hoping someone can help me with optimizing a query that is
taking a really long time because the data set is so large... Basically I
have a large table and I need to select on some date ranges.
Will adding an index for a datetime column help performance for queries of
the form (dtCol >= DATE1) and (dtCol < DATE2)?
-mdb
On Fri, 13 Jul 2007 14:11:21 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Hi all... I'm hoping someone can help me with optimizing a query that is
>taking a really long time because the data set is so large... Basically I
>have a large table and I need to select on some date ranges.
>Will adding an index for a datetime column help performance for queries of
>the form (dtCol >= DATE1) and (dtCol < DATE2)?
>-mdb
The answer is the most common one here: It depends.
If the index were clustered on the date then it certainly will help.
But I would not rush to cluster on the date, the choice of a proper
clustered index is not that simple.
If the date ranges are very narrow compared to the overall table then
a non-clustered index on the date may very well be chosen by the
optimizer and save time. The optimizer is going to estimate the
number of pages it will have to read in random order to use the index,
and compare that to the number of pages it will have to read to scan
the entire table. The smaller number wins.
Without more information on the table, data and query I don't know
what else to say.
Roy Harvey
Beacon Falls, CT
|||Roy Harvey <roy_harvey@.snet.net> wrote in
news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com:

> If the index were clustered on the date then it certainly will help.
> But I would not rush to cluster on the date, the choice of a proper
> clustered index is not that simple.
> If the date ranges are very narrow compared to the overall table then
> a non-clustered index on the date may very well be chosen by the
> optimizer and save time. The optimizer is going to estimate the
> number of pages it will have to read in random order to use the index,
> and compare that to the number of pages it will have to read to scan
> the entire table. The smaller number wins.
> Without more information on the table, data and query I don't know
> what else to say.
>
Well I can tell you that the dates are very evenly spread out. Each row is
an entry from about 200 different clients where each event from a client is
separated by about 3-4 minutes, with very little variation. There is an
index on another column (an integer) and the query is selecting values from
that table where the int value equals a value and the date column is
between a range, and is inner joining that to 3 other tables through a
fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
what you said?
It sounds as if a clustered index would help in this case. Do you agree?
-mdb
|||I really can't tell from the information available.
If you post the table definitions, including keys and indexes, as well
as sizes and the query that is performing poorly someone might be able
to give meaningful advice.
Roy Harvey
Beacon Falls, CT
On Mon, 16 Jul 2007 06:35:38 -0700, Michael Bray
<mbrayATctiusaDOTcom@.you.figure.it.out.com> wrote:

>Roy Harvey <roy_harvey@.snet.net> wrote in
>news:k4uf935qq5jqltamrffo96mgsemsnojorh@.4ax.com :
>
>Well I can tell you that the dates are very evenly spread out. Each row is
>an entry from about 200 different clients where each event from a client is
>separated by about 3-4 minutes, with very little variation. There is an
>index on another column (an integer) and the query is selecting values from
>that table where the int value equals a value and the date column is
>between a range, and is inner joining that to 3 other tables through a
>fourth common table (i.e. A-Z-B, A-Z-C, A-Z-D). Does that alter any of
>what you said?
>It sounds as if a clustered index would help in this case. Do you agree?
>-mdb

help optimizing query

I have a query that is taking about 12 minutes to run. I'm sure there has to be a way to speed it up...i'm just not sure how. any suggestions on how to rewrite this would be much appreciated. i think it has something to do with the "not in" join.

Select
Distinct T.TSNUMB
from
TSR T
where
T.TSSTAT='C'
and T.TSCDAT >= '01-Oct-03'
and T.TSCDAT <= sysdate
and T.TSORGA in (Select distinct O.ORORGA
from OrgCode O
where
O.ORTCON like '123456%'
and T.TSNUMB not in (select distinct w.wcnumb
from
wobaskdtl w
where w.wcbask like '%-V'))You could try using NOT EXISTS instead of NOT IN:

Select
Distinct T.TSNUMB
from
TSR T
where
T.TSSTAT='C'
and T.TSCDAT >= '01-Oct-03'
and T.TSCDAT <= sysdate
and T.TSORGA in (Select distinct O.ORORGA
from OrgCode O
where
O.ORTCON like '123456%'
and not exists (select null
from
wobaskdtl w
where w.wcbask like '%-V' and w.wcnumb = t.tsnumb))

Also make sure your tables have been analyzed and the statistics are not stale (I'm inferring this is Oracle?)|||thank you...not sure i completely understand what is going on there (have to sit down and really look at it) but it works.sql

Help Optimize my Query

that takes 20 seconds may however maximally 5 last
how am I to provide the indices? fill factor?
SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
Massnahme,
dbo.IHLEAktionenStamm.Name AS Aktion,
dbo.IHLEAdressenStamm.Name1 AS KundenName,
dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
dbo.IHLEAktionenPlan.GueltigBis,
dbo.IHLEAktionenPlan.Wiedervorlage, dbo.IHLEAktionenPlan.WiedervorlageText,
dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
dbo.IHLEMarketingParameter.StatusKennzeichen AS
StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
LeistungsAngebote,
dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
LeistungsAngebotPruefung,
dbo.IHLEMarketingParameter.Kommunikationsart AS
KommunikationsArt,
dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
KommunikationsArtPruefung
FROM dbo.IHLEAktionenPlan INNER JOIN
dbo.IHLEMassnahmeStamm ON
dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID INNER
JOIN
dbo.IHLEAktionenStamm ON dbo.IHLEAktionenPlan.AktionID
= dbo.IHLEAktionenStamm.AktionID INNER JOIN
dbo.IHLEMassnahmePlan ON
dbo.IHLEAktionenPlan.MassnahmePlanID = dbo.IHLEMassnahmePlan.MassnahmePlanID
INNER JOIN
dbo.IHLEAdressenStamm ON dbo.IHLEAktionenPlan.AdressID
= dbo.IHLEAdressenStamm.AdressID INNER JOIN
dbo.IHLEMarketingParameter ON
dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
(dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
(dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM) AND
(dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
dbo.IHLEAktionenPlan.GeplanteAusfuehrungHi
Have you looked at an execution plan? Does an optimizer available to use
indexes defined on the tables?
"mPiccoli" <mPiccoli@.discussions.microsoft.com> wrote in message
news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
> that takes 20 seconds may however maximally 5 last
> how am I to provide the indices? fill factor?
> SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
> dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
> Massnahme,
> dbo.IHLEAktionenStamm.Name AS Aktion,
> dbo.IHLEAdressenStamm.Name1 AS KundenName,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
> dbo.IHLEAktionenPlan.GueltigBis,
> dbo.IHLEAktionenPlan.Wiedervorlage,
dbo.IHLEAktionenPlan.WiedervorlageText,
> dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
> dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
> dbo.IHLEMarketingParameter.StatusKennzeichen AS
> StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
> LeistungsAngebote,
> dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
> LeistungsAngebotPruefung,
> dbo.IHLEMarketingParameter.Kommunikationsart AS
> KommunikationsArt,
> dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
> KommunikationsArtPruefung
> FROM dbo.IHLEAktionenPlan INNER JOIN
> dbo.IHLEMassnahmeStamm ON
> dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID
INNER
> JOIN
> dbo.IHLEAktionenStamm ON
dbo.IHLEAktionenPlan.AktionID
> = dbo.IHLEAktionenStamm.AktionID INNER JOIN
> dbo.IHLEMassnahmePlan ON
> dbo.IHLEAktionenPlan.MassnahmePlanID =
dbo.IHLEMassnahmePlan.MassnahmePlanID
> INNER JOIN
> dbo.IHLEAdressenStamm ON
dbo.IHLEAktionenPlan.AdressID
> = dbo.IHLEAdressenStamm.AdressID INNER JOIN
> dbo.IHLEMarketingParameter ON
> dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
> WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
> (dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM)
AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
> ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung|||Did you look at the joined and the condition columns, are they indexed ?
Thats would be the main improment.
Jens SUessmeyer.
"mPiccoli" <mPiccoli@.discussions.microsoft.com> schrieb im Newsbeitrag
news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
> that takes 20 seconds may however maximally 5 last
> how am I to provide the indices? fill factor?
> SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
> dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
> Massnahme,
> dbo.IHLEAktionenStamm.Name AS Aktion,
> dbo.IHLEAdressenStamm.Name1 AS KundenName,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
> dbo.IHLEAktionenPlan.GueltigBis,
> dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.WiedervorlageText,
> dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
> dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
> dbo.IHLEMarketingParameter.StatusKennzeichen AS
> StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
> LeistungsAngebote,
> dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
> LeistungsAngebotPruefung,
> dbo.IHLEMarketingParameter.Kommunikationsart AS
> KommunikationsArt,
> dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
> KommunikationsArtPruefung
> FROM dbo.IHLEAktionenPlan INNER JOIN
> dbo.IHLEMassnahmeStamm ON
> dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID
> INNER
> JOIN
> dbo.IHLEAktionenStamm ON
> dbo.IHLEAktionenPlan.AktionID
> = dbo.IHLEAktionenStamm.AktionID INNER JOIN
> dbo.IHLEMassnahmePlan ON
> dbo.IHLEAktionenPlan.MassnahmePlanID =
> dbo.IHLEMassnahmePlan.MassnahmePlanID
> INNER JOIN
> dbo.IHLEAdressenStamm ON
> dbo.IHLEAktionenPlan.AdressID
> = dbo.IHLEAdressenStamm.AdressID INNER JOIN
> dbo.IHLEMarketingParameter ON
> dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
> WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
> (dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
> ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung|||First of all, When you have a query this long, with table and column names a
s
long as this, use aliases as much as possible to improve readability.
Example: Your
"mPiccoli" wrote:

> that takes 20 seconds may however maximally 5 last
> how am I to provide the indices? fill factor?
> SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
> dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
> Massnahme,
> dbo.IHLEAktionenStamm.Name AS Aktion,
> dbo.IHLEAdressenStamm.Name1 AS KundenName,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
> dbo.IHLEAktionenPlan.GueltigBis,
> dbo.IHLEAktionenPlan.Wiedervorlage, dbo.IHLEAktionenPlan.WiedervorlageText
,
> dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
> dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
> dbo.IHLEMarketingParameter.StatusKennzeichen AS
> StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
> LeistungsAngebote,
> dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
> LeistungsAngebotPruefung,
> dbo.IHLEMarketingParameter.Kommunikationsart AS
> KommunikationsArt,
> dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
> KommunikationsArtPruefung
> FROM dbo.IHLEAktionenPlan INNER JOIN
> dbo.IHLEMassnahmeStamm ON
> dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID INNE
R
> JOIN
> dbo.IHLEAktionenStamm ON dbo.IHLEAktionenPlan.Aktion
ID
> = dbo.IHLEAktionenStamm.AktionID INNER JOIN
> dbo.IHLEMassnahmePlan ON
> dbo.IHLEAktionenPlan.MassnahmePlanID = dbo.IHLEMassnahmePlan.MassnahmePlan
ID
> INNER JOIN
> dbo.IHLEAdressenStamm ON dbo.IHLEAktionenPlan.Adress
ID
> = dbo.IHLEAdressenStamm.AdressID INNER JOIN
> dbo.IHLEMarketingParameter ON
> dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
> WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
> (dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM) AN
D
> (dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
> ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung|||First of all, to suggest what indices should exist, it would be good to know
the structure of the tables involved, and the DRI (Declared Referential
Integrity) constraints on the tables... Post the DDL...
Next, When you have a query this long, with table and column names as
long as this, use aliases as much as possible, and indent the major clauses
of teh SQL, to improve readability.
for Example:
The following is identical to your query
SELECT TOP 250 Ap.AktionenPlanID,
Ap.AdressID, MS.Bezeichnung Massnahme,
Ak.Name Aktion, Ad.Name1 KundenName,
Ap.GeplanteAusfuehrung, Ap.GueltigBis,
Ap.Wiedervorlage, Ap.WiedervorlageText,
Ap.NachbearbeitungsCodeID,
Mp.Statuspruefung StatusPruefung,
Mk.StatusKennzeichen StatusKennzeichen,
Mk.Leistungsangebote LeistungsAngebote,
Mp.LeistungsAngebotPruefung LeistungsAngebotPruefung,
Mk.Kommunikationsart KommunikationsArt,
Mp.KommunikationsArtPruefung KommunikationsArtPruefung
FROM dbo.IHLEAktionenPlan Ap
JOIN dbo.IHLEMassnahmeStamm Ms
ON Ms.MassnahmeID = Ap.MassnahmeID
JOIN dbo.IHLEAktionenStamm Ak
ON Ak.AktionID = Ap.AktionID
JOIN dbo.IHLEAktionenStamm Mp
ON Mp.MassnahmePlanID = Ap.MassnahmePlanID
JOIN dbo.IHLEAdressenStamm Ad
ON Ad.AdressID = Ap.AdressID
JOIN dbo.IHLEMarketingParameter Mk
ON Mk.AdressID = Ap.AdressID =
WHERE Ap.AusgefuehrtAm IS NOT NULL
AND MS.LandID = @.LANDID
AND Ap.AusgefuehrtAm >= @.DATEFROM
AND Ap.AusgefuehrtAm <= @.DATETO
ORDER BY Ap.Wiedervorlage, Ap.GeplanteAusfuehrung
"mPiccoli" wrote:

> that takes 20 seconds may however maximally 5 last
> how am I to provide the indices? fill factor?
> SELECT TOP 250 dbo.IHLEAktionenPlan.AktionenPlanID,
> dbo.IHLEAktionenPlan.AdressID, dbo.IHLEMassnahmeStamm.Bezeichnung AS
> Massnahme,
> dbo.IHLEAktionenStamm.Name AS Aktion,
> dbo.IHLEAdressenStamm.Name1 AS KundenName,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung,
> dbo.IHLEAktionenPlan.GueltigBis,
> dbo.IHLEAktionenPlan.Wiedervorlage, dbo.IHLEAktionenPlan.WiedervorlageText
,
> dbo.IHLEAktionenPlan.NachbearbeitungsCodeID,
> dbo.IHLEMassnahmePlan.Statuspruefung AS StatusPruefung,
> dbo.IHLEMarketingParameter.StatusKennzeichen AS
> StatusKennzeichen, dbo.IHLEMarketingParameter.Leistungsangebote AS
> LeistungsAngebote,
> dbo.IHLEMassnahmePlan.LeistungsAngebotPruefung AS
> LeistungsAngebotPruefung,
> dbo.IHLEMarketingParameter.Kommunikationsart AS
> KommunikationsArt,
> dbo.IHLEMassnahmePlan.KommunikationsArtPruefung AS
> KommunikationsArtPruefung
> FROM dbo.IHLEAktionenPlan INNER JOIN
> dbo.IHLEMassnahmeStamm ON
> dbo.IHLEAktionenPlan.MassnahmeID = dbo.IHLEMassnahmeStamm.MassnahmeID INNE
R
> JOIN
> dbo.IHLEAktionenStamm ON dbo.IHLEAktionenPlan.Aktion
ID
> = dbo.IHLEAktionenStamm.AktionID INNER JOIN
> dbo.IHLEMassnahmePlan ON
> dbo.IHLEAktionenPlan.MassnahmePlanID = dbo.IHLEMassnahmePlan.MassnahmePlan
ID
> INNER JOIN
> dbo.IHLEAdressenStamm ON dbo.IHLEAktionenPlan.Adress
ID
> = dbo.IHLEAdressenStamm.AdressID INNER JOIN
> dbo.IHLEMarketingParameter ON
> dbo.IHLEAktionenPlan.AdressID = dbo.IHLEMarketingParameter.AdressID
> WHERE (dbo.IHLEAktionenPlan.AusgefuehrtAm IS NOT NULL) AND
> (dbo.IHLEMassnahmeStamm.LandID = @.LANDID) AND
> (dbo.IHLEAktionenPlan.AusgefuehrtAm >= @.DATEFROM) AN
D
> (dbo.IHLEAktionenPlan.AusgefuehrtAm <= @.DATETO)
> ORDER BY dbo.IHLEAktionenPlan.Wiedervorlage,
> dbo.IHLEAktionenPlan.GeplanteAusfuehrung|||my indices:
PK – AktionenPlanID
IX – AdressID
IX1 – MassnahmeID,AktionID
IX2 – LandID,AusgefuehrtAm
IX3 – Wiedervorlage, GeplanteAusfuerhung
the table contains 1.500.000 data records
if I order by cluse do not use then takes only 4 seconds
can someone explain to me why?
"Jens Sü?meyer" wrote:

> Did you look at the joined and the condition columns, are they indexed ?
> Thats would be the main improment.
> Jens SUessmeyer.
>
> "mPiccoli" <mPiccoli@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
>
>|||my indices:
PK – AktionenPlanID
IX – AdressID
IX1 – MassnahmeID,AktionID
IX2 – LandID,AusgefuehrtAm
IX3 – Wiedervorlage, GeplanteAusfuerhung
the table contains 1.500.000 data records
if I order by cluse do not use then takes only 4 seconds
can someone explain to me why?
"Jens Sü?meyer" wrote:

> Did you look at the joined and the condition columns, are they indexed ?
> Thats would be the main improment.
> Jens SUessmeyer.
>
> "mPiccoli" <mPiccoli@.discussions.microsoft.com> schrieb im Newsbeitrag
> news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
>
>|||my indices:
PK – AktionenPlanID
IX – AdressID
IX1 – MassnahmeID,AktionID
IX2 – LandID,AusgefuehrtAm
IX3 – Wiedervorlage, GeplanteAusfuerhung
the table contains 1.500.000 data records
if I order by cluse do not use then takes only 4 seconds
can someone explain to me why?
"Uri Dimant" wrote:

> Hi
> Have you looked at an execution plan? Does an optimizer available to use
> indexes defined on the tables?
> "mPiccoli" <mPiccoli@.discussions.microsoft.com> wrote in message
> news:FA9C59EB-0FD4-4850-9A6D-51F848BEDE8F@.microsoft.com...
> dbo.IHLEAktionenPlan.WiedervorlageText,
> INNER
> dbo.IHLEAktionenPlan.AktionID
> dbo.IHLEMassnahmePlan.MassnahmePlanID
> dbo.IHLEAktionenPlan.AdressID
> AND
>
>|||my indices:
PK – AktionenPlanID
IX – AdressID
IX1 – MassnahmeID,AktionID
IX2 – LandID,AusgefuehrtAm
IX3 – Wiedervorlage, GeplanteAusfuerhung
the table contains 1.500.000 data records
if I order by cluse do not use then takes only 4 seconds
can someone explain to me why?|||On Tue, 26 Apr 2005 05:36:03 -0700, mPiccoli wrote:

>my indices:
>PK AktionenPlanID
>IX AdressID
>IX1 MassnahmeID,AktionID
>IX2 LandID,AusgefuehrtAm
>IX3 Wiedervorlage, GeplanteAusfuerhung
>the table contains 1.500.000 data records
>if I order by cluse do not use then takes only 4 seconds
>can someone explain to me why?
Hi mPiccoli,
If you leave out the ORDER BY, then SQL Server can return the rows in any
way it sees fit. With the ORDER BY, it has to do extra work.
If you comare execution plans, I guess you'll see one of the following
scenarios:
1. With ORDER BY, the index IX3 is used; without ORDER BY a different
index is used. Apparently, the other index can find the rows you need more
quickly; to satisfy the ORDER BY, the optimizer decides to use the index
that is already in the required order, even if it means that the rest of
the query takes some more work.
2. With ORDER BY, an extra sorting step is added, but the execution plan
doesn't change otherwise. Apparently, the optimizer estimates that getting
the rows in the wrong order first, then reordering them is better than
using a more expensive method to retrieve the rows.
Since I know nothing about your table structure or data, and the query
itself is quite hard to read as well (please do take Bretana's advice to
heart!) this is about all I can say. If you need more advice, you'll have
to provide more information. Check out www.aspfaq.com/5006.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Help optimising SQL Query

Hi,
I have a problem I would really appreciate help with. I am generating
dynamic SQL and need to optimise it. The specific example I am trying to
optimise looks like this:

SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE
(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND
(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2
WHERE T1.DataHeaderID = CT2.DataHeaderID AND (EntityFieldID IN ( 34)
AND (Data LIKE 'SIDE BY SIDE%' )) ))AND
(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CCT3
WHERE T1.DataHeaderID = CCT3.DataHeaderID AND (( Data LIKE 'church%' ))))

I was OK optimising it with just 2 criteria and changed:
SELECT DISTINCT DataHeaderID FROM TB_DataDetailText T1 WHERE

(EntityFieldID IN ( 31) AND (Data LIKE '12BORE%' )) AND

(DataHeaderID=(SELECT DISTINCT DataHeaderID FROM TB_DataDetailText CT2

WHERE T1.DataHeaderID = CT2.DataHeaderID AND (( Data LIKE 'church%' ))))

which took 26 seconds to using a derived table

SELECT distinct T1.DataHeaderID FROM TB_DataDetailText as T1

inner join (SELECT distinct DataHeaderID, Data FROM TB_DataDetailText )
CT2

on T1.DataHeaderID = CT2.DataHeaderID

WHERE

(T1.EntityFieldID IN ( 31) AND (T1.Data LIKE '12BORE%' ))

and (( CT2.Data LIKE 'church%' )) which took 0.03 seconds on the same data.

My problem is I need to write code to generate the SQL for 1 to n criteria
and am struggling to write the query for more than 2

Best regards,

AndrewCompletely untested...(I have two lovely young ladies ripping up some
cardboard behind me which is about an annoying a noise as you can get,
so if it's wrong, you have my excuse. I started on this before they
starting on the cardboard).

I've tried to tidy up your code a bit in the hopes that this points you
in a better direction for solving the problem with n criteria. Maybe
someone could take this further.

SELECT
T1.DataHeaderId

FROM
TB_DataDetailText T1
LEFT JOIN TB_DataDetailText CT2
ON T1.DataHeaderId = CT2.DataHeaderId
LEFT JOIN TB_DataDetailText CCT3
ON CT2.DataHeaderId = CCT3.DataHeaderId

WHERE
T1.EntityFieldId = 31 AND
T1.Data LIKE '12Bore% AND
CT2.EntityFieldId = 34 AND
CT2.Data LIKE 'Side By Side%' AND
CCT3.Data LIKE 'Church%'

Ryan|||Hi Ryan,

Thanks very much. Tidying it up like that makes it much more straightforward
to see what I should do. I have it cracked now and yes, that does work and
blindingly fast (there was a bit of a whinge about quotes but that may have
been me and was easily fixed). It also lets me see a second way that I can
do it.

Thanks again,

Andrew|||No problem.

Ryan

Andrew wrote:
> Hi Ryan,
> Thanks very much. Tidying it up like that makes it much more
straightforward
> to see what I should do. I have it cracked now and yes, that does
work and
> blindingly fast (there was a bit of a whinge about quotes but that
may have
> been me and was easily fixed). It also lets me see a second way that
I can
> do it.
> Thanks again,
> Andrew

HELP ON XML

Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHORIf what you want is the <Customer> element with all attributes, then you can
use this code:
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@.mp:xmltext')
This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@.mp:localname',
CustomerID varchar(10),
ContactName varchar(20))
This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
Please suggest me some ways to achieve this
TIA,
KISHOR|||Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@.mp:xmltext'
Regards,
Kishor.
"Graeme Malcolm" wrote:

> If what you want is the <Customer> element with all attributes, then you c
an
> use this code:
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',2)
> WITH (Customer varchar(100) '@.mp:xmltext')
> This will return the following 2 rows:
> <Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
> On the other hand, if you want the literal strings you specified in your
> post, you could do it by just concatenating the values from the resultset
> like this:
> SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
> ContactName + '"'
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (nodeName varchar(10) '@.mp:localname',
> CustomerID varchar(10),
> ContactName varchar(20))
> This gives you these 2 rows:
> Customer CustomerID ="VINET" ContactName=Paul Henriot"
> Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
> (you could just specify a literal "Customer" instead of retrieving the nod
e
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at lea
st
> I want some round about way, which will solve my problem. I am here pastin
g
> working code.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> This Query will give me result
> CustomerID ContactName
> -- --
> VINET Paul Henriot
> LILAS Carlos Gonzlez
> This is fine but I want to get results like this.
> COLONE
> 'Customer CustomerID="VINET" ContactName="Paul Henriot"'
> 'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
> Please suggest me some ways to achieve this
> TIA,
> KISHOR
>
>
>

HELP ON XML

Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
‘Customer CustomerID="VINET" ContactName="Paul Henriot”’
‘Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"’
Please suggest me some ways to achieve this
TIA,
KISHOR
If what you want is the <Customer> element with all attributes, then you can
use this code:
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',2)
WITH (Customer varchar(100) '@.mp:xmltext')
This will return the following 2 rows:
<Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
On the other hand, if you want the literal strings you specified in your
post, you could do it by just concatenating the values from the resultset
like this:
SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
ContactName + '"'
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (nodeName varchar(10) '@.mp:localname',
CustomerID varchar(10),
ContactName varchar(20))
This gives you these 2 rows:
Customer CustomerID ="VINET" ContactName=Paul Henriot"
Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
(you could just specify a literal "Customer" instead of retrieving the node
name like I've done.)
Cheers,
Graeme
Graeme Malcolm
Principal Technologist
Content Master
- a member of CM Group Ltd.
www.contentmaster.com
"kishor" <kishor@.discussions.microsoft.com> wrote in message
news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
Hi all,
I have a small query, may be this is not supported in SQL 2000. But at least
I want some round about way, which will solve my problem. I am here pasting
working code.
DECLARE @.idoc int
DECLARE @.doc varchar(1000)
SET @.doc ='
<ROOT>
<Customer CustomerID="VINET" ContactName="Paul Henriot">
</Customer>
<Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
</Customer>
</ROOT>'
EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
SELECT *
FROM OPENXML (@.idoc, '/ROOT/Customer',1)
WITH (CustomerID varchar(10),
ContactName varchar(20))
This Query will give me result
CustomerID ContactName
-- --
VINET Paul Henriot
LILAS Carlos Gonzlez
This is fine but I want to get results like this.
COLONE
'Customer CustomerID="VINET" ContactName="Paul Henriot"'
'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
Please suggest me some ways to achieve this
TIA,
KISHOR
|||Hi Graeme Malcolm,
Thanxs for your solution, This worked ...
'@.mp:xmltext'
Regards,
Kishor.
"Graeme Malcolm" wrote:

> If what you want is the <Customer> element with all attributes, then you can
> use this code:
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',2)
> WITH (Customer varchar(100) '@.mp:xmltext')
> This will return the following 2 rows:
> <Customer CustomerID="VINET" ContactName="Paul Henriot"></Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"></Customer>
> On the other hand, if you want the literal strings you specified in your
> post, you could do it by just concatenating the values from the resultset
> like this:
> SELECT nodeName + ' CustomerID ="' + CustomerID + '" ContactName=' +
> ContactName + '"'
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (nodeName varchar(10) '@.mp:localname',
> CustomerID varchar(10),
> ContactName varchar(20))
> This gives you these 2 rows:
> Customer CustomerID ="VINET" ContactName=Paul Henriot"
> Customer CustomerID ="LILAS" ContactName=Carlos Gonzlez"
> (you could just specify a literal "Customer" instead of retrieving the node
> name like I've done.)
> Cheers,
> Graeme
> --
> Graeme Malcolm
> Principal Technologist
> Content Master
> - a member of CM Group Ltd.
> www.contentmaster.com
>
> "kishor" <kishor@.discussions.microsoft.com> wrote in message
> news:C42D06A8-C961-4730-A9C7-A5940F401649@.microsoft.com...
> Hi all,
> I have a small query, may be this is not supported in SQL 2000. But at least
> I want some round about way, which will solve my problem. I am here pasting
> working code.
> DECLARE @.idoc int
> DECLARE @.doc varchar(1000)
> SET @.doc ='
> <ROOT>
> <Customer CustomerID="VINET" ContactName="Paul Henriot">
> </Customer>
> <Customer CustomerID="LILAS" ContactName="Carlos Gonzlez">
> </Customer>
> </ROOT>'
> EXEC sp_xml_preparedocument @.idoc OUTPUT, @.doc
> SELECT *
> FROM OPENXML (@.idoc, '/ROOT/Customer',1)
> WITH (CustomerID varchar(10),
> ContactName varchar(20))
> This Query will give me result
> CustomerID ContactName
> -- --
> VINET Paul Henriot
> LILAS Carlos Gonzlez
> This is fine but I want to get results like this.
> COLONE
> 'Customer CustomerID="VINET" ContactName="Paul Henriot"'
> 'Customer CustomerID="LILAS" ContactName="Carlos Gonzlez"'
> Please suggest me some ways to achieve this
> TIA,
> KISHOR
>
>
>

Help on this

Hello:

I am currently running a query and it is taking like 6 hours to run.

what I do is:

I loop through user's table (20000 users) using a cursor, for each user I call a stored procedure "EXEC ...", then in that called stored procedure, I have an a set of 6 IF statements, and in each if statement I have a call for a simple stored procedure,.

Can anyone tell me, how can i optimize that query?

thanks a lot

Don't use a cursor? We'd need to know more specifics about what it's actually doing to determine if you can do this as a set operation instead of row-by-row (cursor).

Marcie

|||

Hello Marcie:

Thank you for your reply.

I sent you an email from your Blog, it has some codes.

thanks a lot,

|||

Hi SNT2,

I don't have access to that email account during the day, sorry. Also, if you'll post the code here (or at least an overview of what it does), other people will be able to help you with your problem too.

Take care,

Marcie

|||

Hello, I will list the Stored Procedures here: As I said, there is a main procedure, which loops using a cursor through all the records, and upon getting each account I call a method, the first method is the general one:

CREATE PROCEDURE ActiveUserAnalysis_AnalyzeUsers( @.AnalysisPeriod int = 30, -- the number of days over which the refills are checked @.CustGrpId int = 101, -- the group that we want to analyze @.AnalysisId int out)ASBEGIN SET NOCOUNT ON --create the analysis record INSERT INTO ActiveUserAnalysis ( AnalysisTime, AnalysisPeriod, CustGrpId) VALUES ( getdate(), @.AnalysisPeriod, 101) SET @.AnalysisId = @.@.identityDECLARE @.UserCount int SET @.UserCount = 0 --loop over all active users and analyze them DECLARE cActiveCustomers CURSORREAD_ONLYFOR select customerid, username from activecustomersview where custgrpid=@.CustGrpIdDECLARE @.customerid intDECLARE @.username varchar(30)OPEN cActiveCustomersFETCH NEXT FROM cActiveCustomers INTO @.customerid, @.usernameWHILE (@.@.fetch_status <> -1)BEGINIF (@.@.fetch_status <> -2)BEGIN SET @.UserCount = @.UserCount + 1 EXEC ActiveUserAnalysis_AnalyzeActiveUser @.AnalysisId, @.AnalysisPeriod, @.CustomerId, @.UserName   ENDFETCH NEXT FROM cActiveCustomers INTO @.customerid, @.usernameENDCLOSE cActiveCustomersDEALLOCATE cActiveCustomers UPDATE ActiveUserAnalysis SET ActiveUserCount = @.UserCount WHERE AnalysisId = @.AnalysisId SELECT Classification, count(*) FROM ActiveUserAnalysis_Results WHERE AnalysisId = @.AnalysisId GROUP BY ClassificationENDGO

Now the method we are calling above for each user is:

CREATE procedure ActiveUserAnalysis_AnalyzeActiveUser( @.AnalysisId int, @.AnalysisPeriod int, @.customerid int, @.username varchar(30))asbegin DECLARE @.TokenType varchar(4) DECLARE @.Expires datetime DECLARE @.Value int DECLARE @.LastActivity datetime DECLARE @.RefillType varchar(30) DECLARE @.LastRefillDate datetime DECLARE @.RefillCount int DECLARE @.TBSUserName varchar(30) --First collect info EXEC ActiveUserAnalysis_GetUsageToken @.customerid, @.TokenType out, @.Value out, @.Expires out SELECT TOP 1 @.LastActivity = LastActivity FROM PPPAccount WHERE CustomerId = @.CustomerId --Classify user -- 1- Refill EXEC ActiveUserAnalysis_CheckUserLastRefills @.CustomerId, @.AnalysisPeriod, @.RefillType out, @.LastRefillDate out if @.@.rowcount > 0 BEGIN INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'Refill', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires) RETURN END -- 2- NoLogin: He hasn't logged in at all during period If datediff (d, @.LastActivity, getdate()) > @.AnalysisPeriod BEGIN INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'NoLogin', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires) RETURN END --3 Other. If we reached here, then the user does not fit with any of the above categories INSERT INTO ActiveUserAnalysis_Results (AnalysisId, customerid, classification, lastactivity, refilltype, lastrefilldate, tokentype, value, expires) VALUES (@.AnalysisId, @.customerid, 'Other', @.LastActivity, @.RefillType, @.LastRefillDate, @.TokenType, @.Value, @.Expires)ENDGO

I didn't include all cases, but i listed only 3 of them.

Then in every case I am calling a procedure too.

Can that be optimzed?

Thanks Marcie

|||

Yes, but it will take some significant work. You haven't included the stored procedures/views that this depends on, so I can't really give you code that will work 100%.

Start off by creating a view for many of the stored procedures that this thing depends on... Can you post the code for say...

ActiveUserAnalysis_CheckUserLastRefills? Hopefully that doesn't call any more stored procedures/views.

|||

Hello, thank you for helping me.

The SP is as follows:

CREATE procedure ActiveUserAnalysis_CheckUserLastRefills(@.CustomerId int,@.AnalysisPeriod int, -- in days,@.Refilltype char(5) out,@.LastRefillDate datetime out)asselect top 1 @.RefillType=c.ISKCatCode, @.LastRefillDate = datepurchasedfrom isk i inner join iskcategory c on i.iskcatid = c.iskcatidwhere customerid = @.customerid and datediff(d, datepurchased, getdate()) <= @.AnalysisPeriod and i.CreatedNewCustomer = 0order by datepurchased descGO

Thank you.|||

Hello Marcie:

Can you help in that please?

thanks

|||

SomeNewTricks2 wrote:
I am currently running a query and it is taking like 6 hours to run.
what I do is:
I loop through user's table (20000 users) using a cursor, foreach user I call a stored procedure "EXEC ...", then in that calledstored procedure, I have an a set of 6 IF statements, and in each ifstatement I have a call for a simple stored procedure,.
Can anyone tell me, how can i optimize that query?

That's an awful big haystack. Have you pinpointed thebottleneck? I'd suggest running the query from Query Analyzerwith Show Execution Plan turned on. This should give you an ideaof the slow parts.

Also, this will not likely make an appreciable difference in yourexecution time, but instead of a cursor I'd use a #temp table (see anexample of this approach in this post:http://forums.asp.net/511669/ShowPost.aspx).|||

SomeNewTricks2 wrote:
Hello Marcie:
Can you help in that please?
thanks

My gut feel is still that you could probably do all of this with one (large) Update statement, rather than looping through thousands of items and doing them one at a time. If that gets too complex (or is not possible), using a #temp table as Terri suggests will make this job easier, and much faster than the cursor approach.

Marcie

|||

Thank you all.

Do you think using a loop over a temp table, is better than cursor?

What about the "if" statements, is using them ok?

thanks

|||

SomeNewTricks2 wrote:
Do you think using a loop over a temp table, is better than cursor?

Yes I do, that's why I suggested it. Here's a little background information:SQL Server Temp Table Performance.

SomeNewTricks2 wrote:
What about the "if" statements, is using them ok?

I think using them is OK, but as far as I understand it, when SQLServer compiles the stored procedure it will use the execution planbased on whichever parameters were passed to it the first timethrough. So the execution plan may not be optimized for otherparameters. But I have also read that SQL Server often recompilesstored procedures, so I don't know how much of an issue that truly is.

Again, I urge you to identifiy your bottleneck(s). You havepresented a huge haystack to look through, and it could be that onesmall part needing optimization is causing all of the performanceissues. We don't really have enough information to be able tohelp you.

|||

Sorry, been away for a few days... I didn't see that you were stuffing some of values from ActiveUserAnalysis_GetUsageToken in your inserts, could you post that code? As long as it's pretty simple, I'll have some working code for ya that should be magnitudes faster than what you are currently doing by using set-based rather than cursor-if based logic.

Cursoring over a temp table won't be faster than cursoring over a query, however, you will have less transaction collisions (possibly). The article the above poster referenced, is saying temp tables are faster if by using one you can avoid a cursor and use set-based logic, which is what you'll probably have to do, but possibly not, but it doesn't eliminate the need to move away from the cursor-if logic.

|||

First of all, thank you Terri for your help.

Here is the code:

create procedure ActiveUserAnalysis_GetUsageToken
(
@.customerid int,
@.TokenType varchar(4) out,
@.Value int out,
@.Expires datetime out
)
as
SELECT TOP 1 /* get only first (highest priority) token */
@.TokenType=t.TokenTypeId, @.Value=u.Value, @.Expires=u.Expires
FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.customerid = @.customerid
AND u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)
ORDER BY y.Priority, u.Created

Thanks a lot.

|||

Ok, let's start here. First step is to unroll this stored procedure into a view so that we can reuse it later. Heh, it would figure that you would have this buried somewhere, this is definately the hardest part of unravelling your cursor logic, so here goes. I am going to make the assumption that a single user can not have two (or more) tokens created with the same priority created at the same "Created". If this assumption is not correct, please let me know what combination of fields would be required to ALWAYS return no more than a single record.

Coding by hand here, so might be a syntax error somewhere (sorry).

CREATE VIEW vw_CustomerUsageToken

SELECT t1.customerid,t1.TokenTypeId,t1.Value,t1.Expires
FROM (SELECT u.customerid,t.TokenTypeId,u.Value,u.Expires,y.priority,u.created FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)) t1

LEFT JOIN (SELECT u.customerid,t.TokenTypeId,u.Value,u.Expires,y.priority,u.created FROM UsageToken u
INNER JOIN UsageTokenTemplate t
ON u.UsageTokenTemplId = t.UsageTokenTemplId
INNER JOIN TokenType y
ON t.TokenTypeId = y.TokenTypeId
WHERE u.status<100 /* could be frozen */
AND (u.Expires is null or u.Expires > getdate()) /*did not expire*/
AND (u.Value is null or u.Value>0)) t2 on t1.customerid=t2.customerid AND (t2.priority<t1.priority OR (t2.priority=t1.priority AND t2.created<t1.created))

WHERE t2.customerid IS NULLsql

Wednesday, March 28, 2012

Help on SQL Query

Hi,

I have a table called employee. I have fields like Emp.Nbr and login.date. I want to retrieve unique data of employee numbers. But I want unique with max(login.date) of an employee.

Ex: 111 7/19/2007

222 6/13/2007

111 7/25/2007

333 5/21/2007

222 7/12/2007

I want out put like

111 7/25/2007

222 7/12/2007

333 5/21/2007

How to write query for the above out put.

Kindly help.

Thanks

SELECT EMP.NBR,MAX(login.date)

FROM EMP INNER JOIN Login

on EMP.ID = Login.EmpId

Group by EMP.NBR

|||

select Nbr,max(LoginDate)

from emp

group by nbr

|||

Here it is,

Code Snippet

Create Table #emp (

[EmpId] Varchar(100) ,

[Date] DateTime

);

Insert Into #emp Values('111','7/19/2007');

Insert Into #emp Values('222','6/13/2007');

Insert Into #emp Values('111','7/25/2007');

Insert Into #emp Values('333','5/21/2007');

Insert Into #emp Values('222','7/12/2007');

Select EmpId, Max(Date) As date From #emp Group By EmpID

|||

hope this will suites your requirement: Here I have used Table Variable instead of Temporary Tables.

DECLARE @.Table TABLE ( Nbr int , LoginDate smalldatetime )

INSERT INTO @.Table (Nbr, LoginDate) VALUES(111, '7/19/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(222, '6/13/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(111, '7/25/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(333, '5/21/2007')

INSERT INTO @.Table (Nbr, LoginDate) VALUES(222, '7/12/2007')

SELECT

Nbr, max(LoginDate)

FROM @.Table

GROUP BY Nbr

Regards,

Prashanthi.

Friday, March 30, 2012

help out of control query

help out of control query

help out of control query

Help optimizing query for large data set

Help optimizing query for large data set

Help optimizing query for large data set

help optimizing query

Help Optimize my Query

Help optimising SQL Query

HELP ON XML

HELP ON XML

HELP ON XML

Help on this

Wednesday, March 28, 2012

Help on SQL Query

sql server codes

Blog Archive

About Me