Query process time

I have a weird issue that I just can't figure out. I have a query that runs on tables that are all located in db x. If I run the query under a different db (like use database y) then it runs in like 11 seconds. If I run the query under the db that all of the tables reside under (use database x) then it takes almost 4 minutes.

Why would it take so much longer to run under the db where all of the data/tables exists? Both dbs are on the same SQL 2005 server.

I just don't get it?

Any ideas?

S

We can't possibly give you a meaningful response without having more information. What is the query, what is the nature of the data, what is different between the two databases, etc.

Perhaps, using your description about, database X has data in it, and database Y does not?

|||If you think that execution plan will be same then why SQl statement take different time on different server. But have you checked the CPU Cost and I/O cost.

Query Prepration taking a long time

I have a query - it's moderately comploex, but nothing earth shattering. Problem is, this query takes forever to compile/prepare.

Clicking on the Execute button in SSMS, this query takes 17 seconds to run. Clicking on the syntax check takes 16 seconds to run.

Using the query in an SSRS report is even worse, with the report designer going 100% compute bound for minute at a time on leaving the query editor tab (or executing the query).

Are there any tricks to getting preparation time under control? This feels like there's something pathologically wrong that's making query preparation so slow on this query.

Update: I've found that I can perturb the query in a variety of ways (e.g. commenting out small sub-sections) and end up with queries that parse almost instantly, while other variations take even longer to parse. Unfortunately, none of the variations that parse quickly will produce the correct result - I've commented out some important piece each time.

Cache Warming is one obvious solution it seems.

See the below link.

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!994.entry

Regards,

Jiju

|||

Cache warming doesn't explain it, I don't think. This 16-second overhead is experienced every time I run the query, even when there's no intervening activity at all. I would expect a cache-related delay to come after query prep, for that matter - so I'd see the delay on executing the query, but not in a simple syntax check.

|||

I think I know what's going on. My suspicion is that "parsing" from SSMS or "preparation" from SSRS is running enough of the query to produce the set of tuples for each axis of the query. The query expression used for rows is of the form

Code Snippet

topCount(

order(

filter(

{ ... big expression },

[Measure] > @.Parameter

),

[Measure],

bdesc

),

@.Parameter

)

So determining the set of rows requires actually running the topCout/order/filter over the entire dimensional space of the "big expression", which is many thousands of members - in fact, it's probably the majority of the execution time of the entire query. The measure that's used for the filter and order expressions is the most expensive in the query - calculating it pretty much requires calculating everything else.

So, "preparation" takes so long because SSAS is doing >95% of the work required to run the entire query as a part of this operation.

That's my theory anyway - does it hold water?

|||

Actually, I am pretty sure that SSAS executes the query twice when you preview from the report designer, I'm not so sure about the parsing from SSMS, but seeing as it works with relational datasources as well there is a good chance that it too is issuing an OLEDB command to execute for prepare when you click the parse button.

SSAS does not have the concept of running a query for prepare only like SQL does, for a lot of queries the columns can only be determined by actually running the query, so SSAS ends up actually runs the entire query twice. You would be able to confirm this by using SQL Profiler to trace the SSAS activity while you are working in the report designer.

A simple thing like putting the NON EMPTY clause on an axis means that until all the data has been retrieved, the shape of the final result set cannot be determined.

Query Prepration taking a long time

I have a query - it's moderately comploex, but nothing earth shattering. Problem is, this query takes forever to compile/prepare.

Clicking on the Execute button in SSMS, this query takes 17 seconds to run. Clicking on the syntax check takes 16 seconds to run.

Using the query in an SSRS report is even worse, with the report designer going 100% compute bound for minute at a time on leaving the query editor tab (or executing the query).

Are there any tricks to getting preparation time under control? This feels like there's something pathologically wrong that's making query preparation so slow on this query.

Update: I've found that I can perturb the query in a variety of ways (e.g. commenting out small sub-sections) and end up with queries that parse almost instantly, while other variations take even longer to parse. Unfortunately, none of the variations that parse quickly will produce the correct result - I've commented out some important piece each time.

Cache Warming is one obvious solution it seems.

See the below link.

http://cwebbbi.spaces.live.com/blog/cns!7B84B0F2C239489A!994.entry

Regards,

Jiju

|||

Cache warming doesn't explain it, I don't think. This 16-second overhead is experienced every time I run the query, even when there's no intervening activity at all. I would expect a cache-related delay to come after query prep, for that matter - so I'd see the delay on executing the query, but not in a simple syntax check.

|||

I think I know what's going on. My suspicion is that "parsing" from SSMS or "preparation" from SSRS is running enough of the query to produce the set of tuples for each axis of the query. The query expression used for rows is of the form

Code Snippet

topCount(

order(

filter(

{ ... big expression },

[Measure] > @.Parameter

),

[Measure],

bdesc

),

@.Parameter

)

So determining the set of rows requires actually running the topCout/order/filter over the entire dimensional space of the "big expression", which is many thousands of members - in fact, it's probably the majority of the execution time of the entire query. The measure that's used for the filter and order expressions is the most expensive in the query - calculating it pretty much requires calculating everything else.

So, "preparation" takes so long because SSAS is doing >95% of the work required to run the entire query as a part of this operation.

That's my theory anyway - does it hold water?

|||

Actually, I am pretty sure that SSAS executes the query twice when you preview from the report designer, I'm not so sure about the parsing from SSMS, but seeing as it works with relational datasources as well there is a good chance that it too is issuing an OLEDB command to execute for prepare when you click the parse button.

SSAS does not have the concept of running a query for prepare only like SQL does, for a lot of queries the columns can only be determined by actually running the query, so SSAS ends up actually runs the entire query twice. You would be able to confirm this by using SQL Profiler to trace the SSAS activity while you are working in the report designer.

A simple thing like putting the NON EMPTY clause on an axis means that until all the data has been retrieved, the shape of the final result set cannot be determined.

Query plan utilization vs CPU time...

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
-
SELECT Index Seek
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
-
SELECT Index Scan
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

STAT TIME--
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

STAT TIME--

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

Btw, I'm using SQL Server 2005. Any suggestion please, even just tell me if this is the right place to post this question. Hope some of you know the answer to this. Thanks!

Query plan utilization vs CPU time...

I was tuning a query testing out SARG with these two queries:

select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
select col1 from table1 (nolock) where col1 like '%ABC%'

I flushed out the cache, added an index to col1, then ran those two together. Provided below are the actual query plan and stat time:

Query 1: Query cost (relative to the batch): 0%
select col1 from table1 (nolock) where col1 like '#,%ABC%' or col1 like 'BC,%ABC%'
-
SELECT Index Seek
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

Query 2: Query cost (relative to the batch): 100%
select col1 from table1 (nolock) where col1 like '%ABC%'
-
SELECT Index Scan
Cost:0% <- [DB1].[dbo].[table1].[Idx..]

Cost: 100%

STAT TIME--
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 7 ms.

(3 row(s) affected)
Table 'table1'. Scan count 2, logical reads 2932, physical reads 0, read-ahead reads 18, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 938 ms, elapsed time = 943 ms.

(3 row(s) affected)
Table 'table1'. Scan count 1, logical reads 2927, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

SQL Server Execution Times:
CPU time = 515 ms, elapsed time = 505 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 1 ms.

STAT TIME--

As expected, SARGable Query 1 did a nonclustered index seek and nonSARGable Query 2 did an index scan instead. According to the query plan, Query 1 consumed 0% relative to the batch whereas Query 2 is 100%. When I checked the CPU time, I was a bit confused because Query 1 showed CPU time of 938ms whereas Query 2 showed 515ms. I triple checked and every time I got similar results. I am sure I'm missing something, could someone please tell me what I'm missing? Thanks a bunch!

Btw, I'm using SQL Server 2005. Any suggestion please, even just tell me if this is the right place to post this question. Hope some of you know the answer to this. Thanks!
sql

Query performance with order by clause?

Hi all,

Just wondering if anyone can tell me if an order by clause on a select
query would have any impact on the time it takes to retrieve results?

Essentially I'm selecting Top 1 out of a table via various criteria
and currently getting it back without an order by clause. The order by
would only include the column that has the clustered primary index on
it.

Can anyone tell me if in theory this will slow the query down?

Many thanks in advance!

Much warmth,

MurrauM Wells (planetquirky@.planetthoughtful.org) writes:
> Just wondering if anyone can tell me if an order by clause on a select
> query would have any impact on the time it takes to retrieve results?
> Essentially I'm selecting Top 1 out of a table via various criteria
> and currently getting it back without an order by clause. The order by
> would only include the column that has the clustered primary index on
> it.
> Can anyone tell me if in theory this will slow the query down?

For most situations this is an uninteresting question. TOP 1 with an
ORDER BY means "give me one row, I don't care which", but it's not good
for a random selection.

So if you need your row to be deterministically selected, then you must
have an ORDER BY clause.

The cost for the ORDER BY clause is likely to be marginal, if the order
by columns agrees with the clustered index.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Query performance problem

I'm having trouble figuring out why a query is having
varying response time. The following query takes anywhere
from a 2 seconds to 20 minutes:
select *
from tbl (nolock)
where ACTIVE = 1
There are 17 rows returned in the table. The largest data
in the text field is 34 KB, with most around 20 KB.
Anybody have any ideas?
Table structure is as follows:
CREATE TABLE [tbl] (
[ID] [int] IDENTITY (40, 1) NOT NULL ,
[ACTIVE] [bit] NOT NULL ,
[NAME] [varchar] (50) COLLATE NULL ,
[FILE] [text] NULL ,
CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
(
[SHEET_ID]
)
)What indexes do you have? And how many rows in the table? What query plan
does the optimizer select? Does it use an index on the Active column?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||There is only one index, The PK on the field "ID" (see DDL
below).
There are 21 rows in the table, the query returns 17.
Notice that there is a text column in the tbl (20-30KB).
The optimizer does a full tablescan on the table. Also
note, I am running a trace for that table and there is not
any update DML.
Thanks,
Mark

>--Original Message--
>What indexes do you have? And how many rows in the table?
What query plan
>does the optimizer select? Does it use an index on the
Active column?
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <anonymous@.discussions.microsoft.com>
wrote in message
>news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
anywhere
data
>
>.
>|||OK. Well, you could try creating a supporting index for the query, but it
seems like the major issue here is actually accessing the BLOB data. Bit
still it is not so much data that I would suspect so varying response times.
And just to be certain, check for blocking (I know you mention you are
running a profiler trace, but just to be certain). I guess you could
investigate where the wait time is, a recent SQL Server Magazine had a nice
article regarding this.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
> There is only one index, The PK on the field "ID" (see DDL
> below).
> There are 21 rows in the table, the query returns 17.
> Notice that there is a text column in the tbl (20-30KB).
> The optimizer does a full tablescan on the table. Also
> note, I am running a trace for that table and there is not
> any update DML.
> Thanks,
> Mark
>
> What query plan
> Active column?
> wrote in message
> anywhere
> data|||Have you attempted to call just the column names instead of using the "*"?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:e84f01c40b99$728b2540$a101280a@.phx.gbl...
> I'm having trouble figuring out why a query is having
> varying response time. The following query takes anywhere
> from a 2 seconds to 20 minutes:
> select *
> from tbl (nolock)
> where ACTIVE = 1
> There are 17 rows returned in the table. The largest data
> in the text field is 34 KB, with most around 20 KB.
> Anybody have any ideas?
> Table structure is as follows:
> CREATE TABLE [tbl] (
> [ID] [int] IDENTITY (40, 1) NOT NULL ,
> [ACTIVE] [bit] NOT NULL ,
> [NAME] [varchar] (50) COLLATE NULL ,
> [FILE] [text] NULL ,
> CONSTRAINT [PK_tbl] PRIMARY KEY NONCLUSTERED
> (
> [SHEET_ID]
> )
> )|||Another table has the "id" field as a fk. Could it be that
an exclusive lock is placed on the "id" when the other
table is inserted or updated?

>--Original Message--
>OK. Well, you could try creating a supporting index for
the query, but it
>seems like the major issue here is actually accessing the
BLOB data. Bit
>still it is not so much data that I would suspect so
varying response times.
>And just to be certain, check for blocking (I know you
mention you are
>running a profiler trace, but just to be certain). I
guess you could
>investigate where the wait time is, a recent SQL Server
Magazine had a nice
>article regarding this.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Mark DeWaard" <mdewaard@.idtdna.com> wrote in message
>news:b74201c40b9f$660907f0$a601280a@.phx.gbl...
DDL
not
table?
>
>.
>|||Yes, that is possible.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mark DeWaard" <anonymous@.discussions.microsoft.com> wrote in message
news:c49201c40c6c$0fc53440$a601280a@.phx.gbl...
> Another table has the "id" field as a fk. Could it be that
> an exclusive lock is placed on the "id" when the other
> table is inserted or updated?
>
> the query, but it
> BLOB data. Bit
> varying response times.
> mention you are
> guess you could
> Magazine had a nice
> DDL
> not
> table?

Query Performance

Hello all-

I have a general question. I realize that joins are most of the time better than subqueries. Now that we have the derived tables, if I put the subquery as a derived table and join it with the other tables, will it make any difference in the performance?

eg:

SELECT EmpId
FROM Emp
WHERE EmpSalary = (SELECT MAX(EmpSalary)
FROM Emp Emp2)

Vs

SELECT Emp1.EmpId
FROM Emp Emp1
INNER JOIN (SELECT MAX(EmpSalary) FROM Emp) Emp2
ON Emp1.EmpSalary = Emp2.EmpSalary

(I know that there are better ways of writing this query, sorting my empsalary and getting the top 1 etc. This is just an example to demostrate my question.)

Appreciate your time
ThnxDid you do a show plan on the 2?

That will show you...but I don't think you'll see a difference...

It depeneds more on the number of rows you have and the indexing...

Query over two columns

Hi,
I have a table that includes two Datetime columns. The first column holds
the date. e.g 2006/5/10 and the second column holds time e,g 08:15:25.
I am having a problems specifing a query that will list the rows in the
table between a date time range.
i.e return all events between 2006/5/8 09:00:00 and 2006/5/10 21:30:00
Can anyone give me any suggesstions on how to do this?
Thanks
Maccawhy do you store it in two datetime columns?
or is it 2 varchar columns?
If so then try this.
select * from [a table]
where cast(datecol + ' ' + timecol as datetime) between
'2006/5/8 09:00:00' and '2006/5/10 21:30:00'|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?|||Would it be an idea to have an overlay view where you make a
combination of both the date and the time field into a real datetime
format?

Query Optimisation

Dear All,
I am facing problem with this procedures,the multiselect queries are
taking lot of time ,is there any other solution apart from indexes
Regards
Eckhart
CREATE proc Rolexi36Sync
as
DECLARE @.date varchar(50),@.ydate varchar(50)
print CONVERT(char(11),(GETDATE()-1),100)
SET @.date =
substring(CONVERT(char(11),(GETDATE()),1
00),5,2)+'-'+substring(CONVERT(char(
11),(GETDATE()),100),1,3)+'- '+substring(CONVERT(char(11),(GETDATE())
,100),8,
4)
SET @.ydate =
substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(cha
r(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),1
00),8,4)
Print @.date
Print @.ydate
insert into
biiod.dbo. data_trans_currentday_test(MobileNo,UA,M
essageID,ContentID,Descrip
tion,MusicLabel,CPID,CPName,ContentType,
Category,SubCategory,TransactionDate
,Units,Unitprice,Shortcode,Servicecode,O
peratorID,CatID,SubCatID,SpecialPack
age,Royalties,Operator,Circ
le,OPGPName)
(select mobileno,
(SELECT CASE ua
when 'unknown' then null
else ua
end) as ua,
(select case remarks
when 'unknown' then null
else remarks
end) as remarks,
contentid,
(select case description
when 'unknown' then null
else description
end) as description,
(select musiclabel from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as musiclable,
(select cpid from datalogs.dbo.contentprovider where cpname =
datalogs.dbo.translogs.cpname) as cpid,
cpname,
contenttype,
(select catname from datalogs.dbo.cont_Catg where catid in (select
catid from cont_master where contentid =
datalogs.dbo.translogs.contentid)) as category,
(select subcatname from datalogs.dbo.cont_subCatg where subcatid in
(select subcatid from cont_master where contentid =
datalogs.dbo.translogs.contentid)) as subcategory,
transactiondate,1 as Units, price,
(select case servicename
when 'AIRTELIVE' then remarks
when 'ALCOMBOPACKREG' then remarks
when 'HINDI' then remarks
when 'NOKIAGAL' then remarks
when 'SUDOKU' then remarks
when 'SUDOKU_APP' then remarks
else NULL
end) as SHORTCODE,
servicename,
(select case servicename
when 'TSTTNEWS' THEN 600
when 'TSTTWAP' THEN 600
when 'TSTT_MMS' THEN 600
when 'AKTEL' THEN 300
when 'TELEMOVIL' THEN 700
when 'COMCEL' THEN 701
when 'QATAR2900' THEN 1
ELSE
(select operatorid from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as operatorid,
(select catid from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as catid,
(select subcatid from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as subcatid,
(select specialpackage from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as specialpackage,
(select Royalties from datalogs.dbo.cont_master where contentid =
datalogs.dbo.translogs.contentid) as Royalties,
(select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT'
when 'ALCLICKWIN6464' then 'Airtel'
when 'ALMMSPORTAL' then 'Airtel'
when 'ALMMSSMSDWN' then 'Airtel'
when 'ALMYALBUM646' then 'Airtel'
when 'HINDU6397' then
substring(remarks,1,PATINDEX('%.6397.%',remarks)-1)
else
(select OPname from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as Operator,
(select case servicename
when 'AKTEL' then 'Bangladesh'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'El Salvador'
when 'COMCEL' THEN 'Gautemala'
when 'TSTTNEWS' then 'Trinidad'
when 'TSTTWAP' then 'Trinidad'
when 'TSTT_MMS' then 'Trinidad'
when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +
6,len(remarks)-PATINDEX('%-%',remarks))
else
(select Circlename from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as Circle,
(select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT MMS'
when 'ALCLICKWIN6464' then 'Airtel Click Win 646'
when 'ALMMSPORTAL' then 'Airtel MMS'
when 'ALMMSSMSDWN' then 'Airtel MMS SMS'
when 'ALMYALBUM646' then 'Airtel My Album'
when 'HINDU6397' then 'Hindu 6397'
else
(select OPname from datalogs.dbo.operator where phoneseries =
substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as OPGPName
from datalogs.dbo.translogs where transactiondate >= @.ydate and
transactiondate < @.date and servicename in
('AIRTELMMS_SUB','ALMYALBUM646','HINDU63
97','MTV','QATAR2900','SIFY'))Eckhart wrote:
> Dear All,
> I am facing problem with this procedures,the multiselect queries are
> taking lot of time ,is there any other solution apart from indexes
Try rewriting your query, replacing subselect to inner join (if
applicable) or left join
,(select musiclabel from dbo.cont_master where contentid
=dbo.translogs.contentid) as musiclable
select ...
,m1.MusicLabel as MusicLabel
...
from dbo.translogs l [left] join dbo.cont_master M1 on
l.contentid=m1.contentid
....
if {translog.contentid} - {int NOT null} use inner join, else - u
se
left join.|||On 28 Jul 2006 00:27:31 -0700, "Eckhart" <n.kopalley@.gmail.com> wrote:
>I am facing problem with this procedures,the multiselect queries are
>taking lot of time ,is there any other solution apart from indexes
Numbers, numbers, please!
How much data, how much time?
What do you get from "set statistics io on"? What does the plan look
like?
The only thing to watch out for is if the plan somehow decides to do
the joins for all records before selecting, so you might try selecting
out of the main translog first into a #temp, then using that as the
source for the complex select. If that's still slow, either there's
something wrong with one of your lookup tables, or, well, I don't
know?!
Josh

>Regards
>Eckhart
>CREATE proc Rolexi36Sync
>as
>DECLARE @.date varchar(50),@.ydate varchar(50)
>print CONVERT(char(11),(GETDATE()-1),100)
>SET @.date =
> substring(CONVERT(char(11),(GETDATE()),1
00),5,2)+'-'+substring(CONVERT(char
(11),(GETDATE()),100),1,3)+'- '+substring(CONVERT(char(11),(GETDATE())
,100),8
,4)
>SET @.ydate =
>substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(ch
ar(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),
100),8,4)
>Print @.date
>Print @.ydate
>insert into
>biiod.dbo. data_trans_currentday_test(MobileNo,UA,M
essageID,ContentID,Description,Mu
sicLabel,CPID,CPName,ContentType,Categor
y,SubCategory,TransactionDate,Units,Unit
pric
e,Shortcode,Servicecode,OperatorID,CatID
,SubCatID,SpecialPackage,Royalties,Opera
tor,
Cir
cle,OPGPName)
>(select mobileno,
>(SELECT CASE ua
>when 'unknown' then null
>else ua
>end) as ua,
>(select case remarks
>when 'unknown' then null
>else remarks
>end) as remarks,
>contentid,
>(select case description
>when 'unknown' then null
>else description
>end) as description,
>(select musiclabel from datalogs.dbo.cont_master where contentid =
>datalogs.dbo.translogs.contentid) as musiclable,
>(select cpid from datalogs.dbo.contentprovider where cpname =
>datalogs.dbo.translogs.cpname) as cpid,
>cpname,
>contenttype,
>(select catname from datalogs.dbo.cont_Catg where catid in (select
>catid from cont_master where contentid =
>datalogs.dbo.translogs.contentid)) as category,
>(select subcatname from datalogs.dbo.cont_subCatg where subcatid in
>(select subcatid from cont_master where contentid =
>datalogs.dbo.translogs.contentid)) as subcategory,
>transactiondate,1 as Units, price,
>(select case servicename
>when 'AIRTELIVE' then remarks
>when 'ALCOMBOPACKREG' then remarks
>when 'HINDI' then remarks
>when 'NOKIAGAL' then remarks
>when 'SUDOKU' then remarks
>when 'SUDOKU_APP' then remarks
>else NULL
>end) as SHORTCODE,
>servicename,
>(select case servicename
>when 'TSTTNEWS' THEN 600
>when 'TSTTWAP' THEN 600
>when 'TSTT_MMS' THEN 600
>when 'AKTEL' THEN 300
>when 'TELEMOVIL' THEN 700
>when 'COMCEL' THEN 701
>when 'QATAR2900' THEN 1
>ELSE
>(select operatorid from datalogs.dbo.operator where phoneseries =
>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as operatorid,
>(select catid from datalogs.dbo.cont_master where contentid =
>datalogs.dbo.translogs.contentid) as catid,
>(select subcatid from datalogs.dbo.cont_master where contentid =
>datalogs.dbo.translogs.contentid) as subcatid,
>(select specialpackage from datalogs.dbo.cont_master where contentid =
>datalogs.dbo.translogs.contentid) as specialpackage,
>(select Royalties from datalogs.dbo.cont_master where contentid =
>datalogs.dbo.translogs.contentid) as Royalties,
>(select case servicename
>when 'AKTEL' then 'Aktel'
>when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
>when 'TELEMOVIL' then 'TeleMovil'
>when 'COMCEL' THEN 'COMCEL'
>when 'TSTTNEWS' then 'TSTT'
>when 'TSTTWAP' then 'TSTT'
>when 'TSTT_MMS' then 'TSTT'
>when 'ALCLICKWIN6464' then 'Airtel'
>when 'ALMMSPORTAL' then 'Airtel'
>when 'ALMMSSMSDWN' then 'Airtel'
>when 'ALMYALBUM646' then 'Airtel'
>when 'HINDU6397' then
>substring(remarks,1,PATINDEX('%.6397.%',remarks)-1)
>else
>(select OPname from datalogs.dbo.operator where phoneseries =
>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as Operator,
>(select case servicename
>when 'AKTEL' then 'Bangladesh'
>when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
>when 'TELEMOVIL' then 'El Salvador'
>when 'COMCEL' THEN 'Gautemala'
>when 'TSTTNEWS' then 'Trinidad'
>when 'TSTTWAP' then 'Trinidad'
>when 'TSTT_MMS' then 'Trinidad'
>when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +
>6,len(remarks)-PATINDEX('%-%',remarks))
>else
>(select Circlename from datalogs.dbo.operator where phoneseries =
>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as Circle,
>(select case servicename
>when 'AKTEL' then 'Aktel'
>when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
>when 'TELEMOVIL' then 'TeleMovil'
>when 'COMCEL' THEN 'COMCEL'
>when 'TSTTNEWS' then 'TSTT'
>when 'TSTTWAP' then 'TSTT'
>when 'TSTT_MMS' then 'TSTT MMS'
>when 'ALCLICKWIN6464' then 'Airtel Click Win 646'
>when 'ALMMSPORTAL' then 'Airtel MMS'
>when 'ALMMSSMSDWN' then 'Airtel MMS SMS'
>when 'ALMYALBUM646' then 'Airtel My Album'
>when 'HINDU6397' then 'Hindu 6397'
>else
>(select OPname from datalogs.dbo.operator where phoneseries =
>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as OPGPName
>from datalogs.dbo.translogs where transactiondate >= @.ydate and
>transactiondate < @.date and servicename in
> ('AIRTELMMS_SUB','ALMYALBUM646','HINDU63
97','MTV','QATAR2900','SIFY'))

Query Optimisation

Dear All,
I am facing problem with this procedures,the multiselect queries are
taking lot of time ,is there any other solution apart from indexes
Regards
Eckhart
CREATE proc Rolexi36Sync
as
DECLARE @.date varchar(50),@.ydate varchar(50)
print CONVERT(char(11),(GETDATE()-1),100)
SET @.date = substring(CONVERT(char(11),(GETDATE()),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()),100),8,4)
SET @.ydate = substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),8,4)
Print @.date
Print @.ydate
insert into
biiod.dbo.data_trans_currentday_test(MobileNo,UA,MessageID,ContentID,Description,MusicLabel,CPID,CPName,ContentType,Category,SubCategory,TransactionDate,Units,Unitprice,Shortcode,Servicecode,OperatorID,CatID,SubCatID,SpecialPackage,Royalties,Operator,Circle,OPGPName)
(select mobileno,
(SELECT CASE ua
when 'unknown' then null
else ua
end) as ua,
(select case remarks
when 'unknown' then null
else remarks
end) as remarks,
contentid,
(select case description
when 'unknown' then null
else description
end) as description,
(select musiclabel from datalogs.dbo.cont_master where contentid = datalogs.dbo.translogs.contentid) as musiclable,
(select cpid from datalogs.dbo.contentprovider where cpname = datalogs.dbo.translogs.cpname) as cpid,
cpname,
contenttype,
(select catname from datalogs.dbo.cont_Catg where catid in (select
catid from cont_master where contentid = datalogs.dbo.translogs.contentid)) as category,
(select subcatname from datalogs.dbo.cont_subCatg where subcatid in
(select subcatid from cont_master where contentid = datalogs.dbo.translogs.contentid)) as subcategory,
transactiondate,1 as Units, price,
(select case servicename
when 'AIRTELIVE' then remarks
when 'ALCOMBOPACKREG' then remarks
when 'HINDI' then remarks
when 'NOKIAGAL' then remarks
when 'SUDOKU' then remarks
when 'SUDOKU_APP' then remarks
else NULL
end) as SHORTCODE,
servicename,
(select case servicename
when 'TSTTNEWS' THEN 600
when 'TSTTWAP' THEN 600
when 'TSTT_MMS' THEN 600
when 'AKTEL' THEN 300
when 'TELEMOVIL' THEN 700
when 'COMCEL' THEN 701
when 'QATAR2900' THEN 1
ELSE
(select operatorid from datalogs.dbo.operator where phoneseries = substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as operatorid,
(select catid from datalogs.dbo.cont_master where contentid = datalogs.dbo.translogs.contentid) as catid,
(select subcatid from datalogs.dbo.cont_master where contentid = datalogs.dbo.translogs.contentid) as subcatid,
(select specialpackage from datalogs.dbo.cont_master where contentid = datalogs.dbo.translogs.contentid) as specialpackage,
(select Royalties from datalogs.dbo.cont_master where contentid = datalogs.dbo.translogs.contentid) as Royalties,
(select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT'
when 'ALCLICKWIN6464' then 'Airtel'
when 'ALMMSPORTAL' then 'Airtel'
when 'ALMMSSMSDWN' then 'Airtel'
when 'ALMYALBUM646' then 'Airtel'
when 'HINDU6397' then
substring(remarks,1,PATINDEX('%.6397.%',remarks)-1)
else
(select OPname from datalogs.dbo.operator where phoneseries = substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as Operator,
(select case servicename
when 'AKTEL' then 'Bangladesh'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'El Salvador'
when 'COMCEL' THEN 'Gautemala'
when 'TSTTNEWS' then 'Trinidad'
when 'TSTTWAP' then 'Trinidad'
when 'TSTT_MMS' then 'Trinidad'
when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +
6,len(remarks)-PATINDEX('%-%',remarks))
else
(select Circlename from datalogs.dbo.operator where phoneseries = substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as Circle,
(select case servicename
when 'AKTEL' then 'Aktel'
when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
when 'TELEMOVIL' then 'TeleMovil'
when 'COMCEL' THEN 'COMCEL'
when 'TSTTNEWS' then 'TSTT'
when 'TSTTWAP' then 'TSTT'
when 'TSTT_MMS' then 'TSTT MMS'
when 'ALCLICKWIN6464' then 'Airtel Click Win 646'
when 'ALMMSPORTAL' then 'Airtel MMS'
when 'ALMMSSMSDWN' then 'Airtel MMS SMS'
when 'ALMYALBUM646' then 'Airtel My Album'
when 'HINDU6397' then 'Hindu 6397'
else
(select OPname from datalogs.dbo.operator where phoneseries = substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
end) as OPGPName
from datalogs.dbo.translogs where transactiondate >= @.ydate and
transactiondate < @.date and servicename in
('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV','QATAR2900','SIFY'))Eckhart wrote:
> Dear All,
> I am facing problem with this procedures,the multiselect queries are
> taking lot of time ,is there any other solution apart from indexes
Try rewriting your query, replacing subselect to inner join (if
applicable) or left join
,(select musiclabel from dbo.cont_master where contentid
=dbo.translogs.contentid) as musiclable
--
select ...
,m1.MusicLabel as MusicLabel
...
from dbo.translogs l [left] join dbo.cont_master M1 on
l.contentid=m1.contentid
....
if {translog.contentid} - {int NOT null} use inner join, else - use
left join.|||On 28 Jul 2006 00:27:31 -0700, "Eckhart" <n.kopalley@.gmail.com> wrote:
>I am facing problem with this procedures,the multiselect queries are
>taking lot of time ,is there any other solution apart from indexes
Numbers, numbers, please!
How much data, how much time?
What do you get from "set statistics io on"? What does the plan look
like?
The only thing to watch out for is if the plan somehow decides to do
the joins for all records before selecting, so you might try selecting
out of the main translog first into a #temp, then using that as the
source for the complex select. If that's still slow, either there's
something wrong with one of your lookup tables, or, well, I don't
know?!
Josh
>Regards
>Eckhart
>CREATE proc Rolexi36Sync
>as
>DECLARE @.date varchar(50),@.ydate varchar(50)
>print CONVERT(char(11),(GETDATE()-1),100)
>SET @.date =>substring(CONVERT(char(11),(GETDATE()),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()),100),8,4)
>SET @.ydate =>substring(CONVERT(char(11),(GETDATE()-1),100),5,2)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),1,3)+'-'+substring(CONVERT(char(11),(GETDATE()-1),100),8,4)
>Print @.date
>Print @.ydate
>insert into
>biiod.dbo.data_trans_currentday_test(MobileNo,UA,MessageID,ContentID,Description,MusicLabel,CPID,CPName,ContentType,Category,SubCategory,TransactionDate,Units,Unitprice,Shortcode,Servicecode,OperatorID,CatID,SubCatID,SpecialPackage,Royalties,Operator,Circle,OPGPName)
>(select mobileno,
>(SELECT CASE ua
>when 'unknown' then null
>else ua
>end) as ua,
>(select case remarks
>when 'unknown' then null
>else remarks
>end) as remarks,
>contentid,
>(select case description
>when 'unknown' then null
>else description
>end) as description,
>(select musiclabel from datalogs.dbo.cont_master where contentid =>datalogs.dbo.translogs.contentid) as musiclable,
>(select cpid from datalogs.dbo.contentprovider where cpname =>datalogs.dbo.translogs.cpname) as cpid,
>cpname,
>contenttype,
>(select catname from datalogs.dbo.cont_Catg where catid in (select
>catid from cont_master where contentid =>datalogs.dbo.translogs.contentid)) as category,
>(select subcatname from datalogs.dbo.cont_subCatg where subcatid in
>(select subcatid from cont_master where contentid =>datalogs.dbo.translogs.contentid)) as subcategory,
>transactiondate,1 as Units, price,
>(select case servicename
>when 'AIRTELIVE' then remarks
>when 'ALCOMBOPACKREG' then remarks
>when 'HINDI' then remarks
>when 'NOKIAGAL' then remarks
>when 'SUDOKU' then remarks
>when 'SUDOKU_APP' then remarks
>else NULL
>end) as SHORTCODE,
>servicename,
>(select case servicename
>when 'TSTTNEWS' THEN 600
>when 'TSTTWAP' THEN 600
>when 'TSTT_MMS' THEN 600
>when 'AKTEL' THEN 300
>when 'TELEMOVIL' THEN 700
>when 'COMCEL' THEN 701
>when 'QATAR2900' THEN 1
>ELSE
>(select operatorid from datalogs.dbo.operator where phoneseries =>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as operatorid,
>(select catid from datalogs.dbo.cont_master where contentid =>datalogs.dbo.translogs.contentid) as catid,
>(select subcatid from datalogs.dbo.cont_master where contentid =>datalogs.dbo.translogs.contentid) as subcatid,
>(select specialpackage from datalogs.dbo.cont_master where contentid =>datalogs.dbo.translogs.contentid) as specialpackage,
>(select Royalties from datalogs.dbo.cont_master where contentid =>datalogs.dbo.translogs.contentid) as Royalties,
>(select case servicename
>when 'AKTEL' then 'Aktel'
>when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
>when 'TELEMOVIL' then 'TeleMovil'
>when 'COMCEL' THEN 'COMCEL'
>when 'TSTTNEWS' then 'TSTT'
>when 'TSTTWAP' then 'TSTT'
>when 'TSTT_MMS' then 'TSTT'
>when 'ALCLICKWIN6464' then 'Airtel'
>when 'ALMMSPORTAL' then 'Airtel'
>when 'ALMMSSMSDWN' then 'Airtel'
>when 'ALMYALBUM646' then 'Airtel'
>when 'HINDU6397' then
>substring(remarks,1,PATINDEX('%.6397.%',remarks)-1)
>else
>(select OPname from datalogs.dbo.operator where phoneseries =>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as Operator,
>(select case servicename
>when 'AKTEL' then 'Bangladesh'
>when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
>when 'TELEMOVIL' then 'El Salvador'
>when 'COMCEL' THEN 'Gautemala'
>when 'TSTTNEWS' then 'Trinidad'
>when 'TSTTWAP' then 'Trinidad'
>when 'TSTT_MMS' then 'Trinidad'
>when 'HINDU6397' then substring(remarks,PATINDEX('%.6397.%',remarks) +
>6,len(remarks)-PATINDEX('%-%',remarks))
>else
>(select Circlename from datalogs.dbo.operator where phoneseries =>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as Circle,
>(select case servicename
>when 'AKTEL' then 'Aktel'
>when 'QATAR2900' then 'STAR MULTIMEDIA 2900'
>when 'TELEMOVIL' then 'TeleMovil'
>when 'COMCEL' THEN 'COMCEL'
>when 'TSTTNEWS' then 'TSTT'
>when 'TSTTWAP' then 'TSTT'
>when 'TSTT_MMS' then 'TSTT MMS'
>when 'ALCLICKWIN6464' then 'Airtel Click Win 646'
>when 'ALMMSPORTAL' then 'Airtel MMS'
>when 'ALMMSSMSDWN' then 'Airtel MMS SMS'
>when 'ALMYALBUM646' then 'Airtel My Album'
>when 'HINDU6397' then 'Hindu 6397'
>else
>(select OPname from datalogs.dbo.operator where phoneseries =>substring(datalogs.dbo.translogs.mobileno,1,len(phoneseries)))
>end) as OPGPName
>from datalogs.dbo.translogs where transactiondate >= @.ydate and
>transactiondate < @.date and servicename in
>('AIRTELMMS_SUB','ALMYALBUM646','HINDU6397','MTV','QATAR2900','SIFY'))

Query Optimazation from Cursor

Hi All,
I have a small T-SQL Bloack which is taking a long time to run. How can I
optimize this?
I have 2 tables "Donor" and "Donations"
Donor Table has "DonorID" and many columns and "SourceID" field. Donations
table has "DonationID", "DonorID", "DatePaid" and "SourceID" and many other
columns.
I want to update the Donor's SourceID (Only for the STATE = "CA") with
Donation's SourceID for that Donor. (Donor may have multiple donations - so
the donation record is the earliest datepaid of the donation)
My Block is below: ---
SET NOCOUNT ON
BEGIN
DECLARE @.DONORID INT,
@.SOURCEID VARCHAR(4),
@.DON_SOURCE VARCHAR(4),
@.TOTAL_UPDT INT,
@.PA_SF_CTR INT,
@.SF_PA_CTR INT,
@.SF_CTR INT,
@.PA_CTR INT,
@.PA_SF_ID INT,
@.SF_PA_ID INT,
@.MSD_ID INT,
@.PA_ID INT
DECLARE @.CONST_FLCC VARCHAR(4),
@.CONST_PA12 VARCHAR(4),
@.FOR_STATE VARCHAR(2)
SELECT @.CONST_FLCC = 'CC', @.CONST_PA12 = 'PA12', @.FOR_STATE = 'CA'
SELECT @.TOTAL_UPDT = 0, @.PA_SF_CTR = 0, @.SF_PA_CTR = 0, @.SF_CTR = 0,
@.PA_CTR = 0
SELECT @.PA_SF_ID = NULL, @.SF_PA_ID = NULL, @.MSD_ID = NULL, @.PA_ID = NULL
CREATE TABLE #DONORLIST
(
ID INT IDENTITY (1,1),
PA_SF_ID INT,
SF_PA_ID INT,
SF_ID INT,
PA_ID INT
)
DECLARE cur INSENSITIVE CURSOR FOR
SELECT DONORID, SOURCEID FROM DONOR WHERE STATE = @.FOR_STATE
FOR READ ONLY
OPEN cur
FETCH NEXT FROM cur INTO @.DONORID, @.DON_SOURCE
WHILE (@.@.FETCH_STATUS = 0)
BEGIN
SELECT @.SOURCEID = NULL
SELECT TOP 1 @.SOURCEID = SOURCEID FROM DONATIONS
WHERE DONORID = @.DONORID
AND DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
@.DONORID)
-- PA39 AND PA43 DONATION WILL MOVE TO PA12 FOR OLDEST DONATIONS
IF ((@.SOURCEID = 'PA39') OR (@.SOURCEID = 'PA43'))
SELECT @.SOURCEID = @.CONST_PA12
-- UPDATE THE DONOR SOURCE.
IF (@.DON_SOURCE <> @.SOURCEID)
BEGIN
--UPDATE DONOR SET SOURCEID = @.SOURCEID WHERE DONORID = @.DONORID
SELECT @.TOTAL_UPDT = @.TOTAL_UPDT + 1
END
-- PA TO SF
IF (@.DON_SOURCE = @.CONST_PA12 AND @.SOURCEID = @.CONST_FLCC)
BEGIN
SELECT @.PA_SF_CTR = @.PA_SF_CTR + 1
SELECT @.PA_SF_ID = @.DONORID
END
-- SF TO PA
ELSE IF (@.DON_SOURCE = @.CONST_FLCC AND @.SOURCEID = @.CONST_PA12)
BEGIN
SELECT @.SF_PA_CTR = @.SF_PA_CTR + 1
SELECT @.SF_PA_ID = @.DONORID
-- FIND DONATIONS THAT ARE HAVING PLEDGEDATE > '01 DEC 2004'.
END
-- SF TO SF (NO CHANGE)
ELSE IF (@.DON_SOURCE = @.CONST_FLCC AND @.SOURCEID = @.CONST_FLCC)
BEGIN
SELECT @.SF_CTR = @.SF_CTR + 1
SELECT @.MSD_ID = @.DONORID
END
-- PA TO PA (NO CHANGE)
ELSE IF (@.DON_SOURCE = @.CONST_PA12 AND @.SOURCEID = @.CONST_PA12)
BEGIN
SELECT @.PA_CTR = @.PA_CTR + 1
SELECT @.PA_ID = @.DONORID
END
INSERT INTO #DONORLIST (PA_SF_ID, SF_PA_ID, SF_ID, PA_ID)
VALUES (@.PA_SF_ID, @.SF_PA_ID, @.MSD_ID, @.PA_ID)
SELECT @.PA_SF_ID = NULL, @.SF_PA_ID = NULL, @.MSD_ID = NULL, @.PA_ID = NULL
SELECT @.DONORID = NULL, @.DON_SOURCE = NULL
FETCH NEXT FROM cur INTO @.DONORID, @.DON_SOURCE
END
CLOSE cur
DEALLOCATE cur
PRINT '1) Total DONOR SOURCE updated (SF to PA / PA to SF): ' +
CAST(@.TOTAL_UPDT AS VARCHAR)
PRINT '2) Total DONOR SOURCE updated from PA to SF: ' + CAST(@.PA_SF_CTR AS
VARCHAR)
PRINT '3) Total DONOR SOURCE updated from SF to PA: ' + CAST(@.SF_PA_CTR AS
VARCHAR)
PRINT '4) DONOR SOURCE was SF and did not change: ' + CAST(@.SF_CTR AS
VARCHAR)
PRINT '5) DONOR SOURCE was PA and did not change: ' + CAST(@.PA_CTR AS
VARCHAR)
PRINT REPLICATE('-', 100)
SELECT PA_SF_ID FROM #DONORLIST WHERE PA_SF_ID IS NOT NULL -- DONORS
CONVERTED FROM PA TO SF
SELECT SF_PA_ID FROM #DONORLIST WHERE SF_PA_ID IS NOT NULL -- DONORS
CONVERTED FROM SF TO PA
SELECT SF_ID FROM #DONORLIST WHERE SF_ID IS NOT NULL -- DONORS REMAINS AS
SF
SELECT PA_ID FROM #DONORLIST WHERE PA_ID IS NOT NULL -- DONORS REMAINS AS
PA
-- DROP TABLE #DONORLIST
END
GO
SET NOCOUNT OFF
=================================
The Donor and Donation table are very large table.
Please help.
Thanks
PrabhatPrabhat,
Don't use a cursor From what I see, you're only doing a single INSERT? You
could change this logic to use a
INSERT INTO #DONORLIST (...)
SELECT (...)
Your select statement would very likely use quite a number of CASE and
COALESCE, but it would most certainly be faster.
Try refactoring it that way.
-- Alex Papadimoulis
"Prabhat" wrote:

> Hi All,
> I have a small T-SQL Bloack which is taking a long time to run. How can I
> optimize this?
> I have 2 tables "Donor" and "Donations"
> Donor Table has "DonorID" and many columns and "SourceID" field. Donations
> table has "DonationID", "DonorID", "DatePaid" and "SourceID" and many othe
r
> columns.
> I want to update the Donor's SourceID (Only for the STATE = "CA") with
> Donation's SourceID for that Donor. (Donor may have multiple donations - s
o
> the donation record is the earliest datepaid of the donation)
> My Block is below: ---
> SET NOCOUNT ON
> BEGIN
> DECLARE @.DONORID INT,
> @.SOURCEID VARCHAR(4),
> @.DON_SOURCE VARCHAR(4),
> @.TOTAL_UPDT INT,
> @.PA_SF_CTR INT,
> @.SF_PA_CTR INT,
> @.SF_CTR INT,
> @.PA_CTR INT,
> @.PA_SF_ID INT,
> @.SF_PA_ID INT,
> @.MSD_ID INT,
> @.PA_ID INT
> DECLARE @.CONST_FLCC VARCHAR(4),
> @.CONST_PA12 VARCHAR(4),
> @.FOR_STATE VARCHAR(2)
> SELECT @.CONST_FLCC = 'CC', @.CONST_PA12 = 'PA12', @.FOR_STATE = 'CA'
> SELECT @.TOTAL_UPDT = 0, @.PA_SF_CTR = 0, @.SF_PA_CTR = 0, @.SF_CTR = 0,
> @.PA_CTR = 0
> SELECT @.PA_SF_ID = NULL, @.SF_PA_ID = NULL, @.MSD_ID = NULL, @.PA_ID = NULL
> CREATE TABLE #DONORLIST
> (
> ID INT IDENTITY (1,1),
> PA_SF_ID INT,
> SF_PA_ID INT,
> SF_ID INT,
> PA_ID INT
> )
> DECLARE cur INSENSITIVE CURSOR FOR
> SELECT DONORID, SOURCEID FROM DONOR WHERE STATE = @.FOR_STATE
> FOR READ ONLY
> OPEN cur
> FETCH NEXT FROM cur INTO @.DONORID, @.DON_SOURCE
> WHILE (@.@.FETCH_STATUS = 0)
> BEGIN
> SELECT @.SOURCEID = NULL
> SELECT TOP 1 @.SOURCEID = SOURCEID FROM DONATIONS
> WHERE DONORID = @.DONORID
> AND DATEPAID = (SELECT MIN(DATEPAID) FROM DONATIONS WHERE DONORID =
> @.DONORID)
> -- PA39 AND PA43 DONATION WILL MOVE TO PA12 FOR OLDEST DONATIONS
> IF ((@.SOURCEID = 'PA39') OR (@.SOURCEID = 'PA43'))
> SELECT @.SOURCEID = @.CONST_PA12
> -- UPDATE THE DONOR SOURCE.
> IF (@.DON_SOURCE <> @.SOURCEID)
> BEGIN
> --UPDATE DONOR SET SOURCEID = @.SOURCEID WHERE DONORID = @.DONORID
> SELECT @.TOTAL_UPDT = @.TOTAL_UPDT + 1
> END
> -- PA TO SF
> IF (@.DON_SOURCE = @.CONST_PA12 AND @.SOURCEID = @.CONST_FLCC)
> BEGIN
> SELECT @.PA_SF_CTR = @.PA_SF_CTR + 1
> SELECT @.PA_SF_ID = @.DONORID
> END
> -- SF TO PA
> ELSE IF (@.DON_SOURCE = @.CONST_FLCC AND @.SOURCEID = @.CONST_PA12)
> BEGIN
> SELECT @.SF_PA_CTR = @.SF_PA_CTR + 1
> SELECT @.SF_PA_ID = @.DONORID
> -- FIND DONATIONS THAT ARE HAVING PLEDGEDATE > '01 DEC 2004'.
> END
> -- SF TO SF (NO CHANGE)
> ELSE IF (@.DON_SOURCE = @.CONST_FLCC AND @.SOURCEID = @.CONST_FLCC)
> BEGIN
> SELECT @.SF_CTR = @.SF_CTR + 1
> SELECT @.MSD_ID = @.DONORID
> END
> -- PA TO PA (NO CHANGE)
> ELSE IF (@.DON_SOURCE = @.CONST_PA12 AND @.SOURCEID = @.CONST_PA12)
> BEGIN
> SELECT @.PA_CTR = @.PA_CTR + 1
> SELECT @.PA_ID = @.DONORID
> END
> INSERT INTO #DONORLIST (PA_SF_ID, SF_PA_ID, SF_ID, PA_ID)
> VALUES (@.PA_SF_ID, @.SF_PA_ID, @.MSD_ID, @.PA_ID)
> SELECT @.PA_SF_ID = NULL, @.SF_PA_ID = NULL, @.MSD_ID = NULL, @.PA_ID = NULL
> SELECT @.DONORID = NULL, @.DON_SOURCE = NULL
> FETCH NEXT FROM cur INTO @.DONORID, @.DON_SOURCE
> END
> CLOSE cur
> DEALLOCATE cur
> PRINT '1) Total DONOR SOURCE updated (SF to PA / PA to SF): ' +
> CAST(@.TOTAL_UPDT AS VARCHAR)
> PRINT '2) Total DONOR SOURCE updated from PA to SF: ' + CAST(@.PA_SF_CTR A
S
> VARCHAR)
> PRINT '3) Total DONOR SOURCE updated from SF to PA: ' + CAST(@.SF_PA_CTR A
S
> VARCHAR)
> PRINT '4) DONOR SOURCE was SF and did not change: ' + CAST(@.SF_CTR AS
> VARCHAR)
> PRINT '5) DONOR SOURCE was PA and did not change: ' + CAST(@.PA_CTR AS
> VARCHAR)
> PRINT REPLICATE('-', 100)
> SELECT PA_SF_ID FROM #DONORLIST WHERE PA_SF_ID IS NOT NULL -- DONORS
> CONVERTED FROM PA TO SF
> SELECT SF_PA_ID FROM #DONORLIST WHERE SF_PA_ID IS NOT NULL -- DONORS
> CONVERTED FROM SF TO PA
> SELECT SF_ID FROM #DONORLIST WHERE SF_ID IS NOT NULL -- DONORS REMAINS
AS
> SF
> SELECT PA_ID FROM #DONORLIST WHERE PA_ID IS NOT NULL -- DONORS REMAINS
AS
> PA
> -- DROP TABLE #DONORLIST
> END
> GO
> SET NOCOUNT OFF
>
> =================================
> The Donor and Donation table are very large table.
> Please help.
> Thanks
> Prabhat
>
>|||Hi,
The Insert is not Important. I am inserting to the Temp table to know the
Donor Records that are getting updated.
That is my fault. The Update statement was commented by mistake. Actually I
wanted to Update the Donrs SourceID from the Donations SourceID as per the
Logic in the Cursor.
The Insert is Just to keep track of the Donor Records that are getting
updated for different Instance only.
Thanks
Prabhat
"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:D2866C6E-6255-4E9F-95BC-EB1E99B72947@.microsoft.com...
> Prabhat,
> Don't use a cursor From what I see, you're only doing a single INSERT? You
> could change this logic to use a
> INSERT INTO #DONORLIST (...)
> SELECT (...)
> Your select statement would very likely use quite a number of CASE and
> COALESCE, but it would most certainly be faster.
> Try refactoring it that way.
> -- Alex Papadimoulis
> "Prabhat" wrote:
>
I
Donations
other
so
NULL
NULL
AS
AS
REMAINS AS
REMAINS AS|||The cursor and loop is surely unnecessary but it's difficult to
understand what you want from this incomplete fragment of code. The
best way to get help is to post DDL (CREATE TABLE statements), sample
data (INSERT statements) and show your required end result. See:
http://www.aspfaq.com/etiquette.asp?id=5006
David Portas
SQL Server MVP
--|||Hi David,
Just see the "Help need for Query - Urgent" Subject in this group for the
details.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1116319917.557377.111950@.f14g2000cwb.googlegroups.com...

> The cursor and loop is surely unnecessary but it's difficult to
> understand what you want from this incomplete fragment of code. The
> best way to get help is to post DDL (CREATE TABLE statements), sample
> data (INSERT statements) and show your required end result. See:
> http://www.aspfaq.com/etiquette.asp?id=5006
> --
> David Portas
> SQL Server MVP
> --
>

query on sysprocesses...

I was trying to findout processes which are running for long time and came across few entries in sysprocess where Last_Batch column shows '01/01/1900'

It is a usual practice for SQL coders to put a default date like this if they don't have a valid date, does sysprocesses table does the same thing ?Hi

It is not the best, but this table dates back to when SQL Server was produced by Sybase. A long history of backward-compatability. Microsoft has changed the system tables in SQL Server 2005 (sys.dm_exec_sessions in this case)

Regards
Mike|||No, this is not the way I would have done it. This is a token date, which more or less means the engine is saying, I don't have information about when this process started. I would rather see NULL here, though the engine should be able to figure out when it first saw this process (?). I can see novice DBAs reaching for the panic button, hey, this process has been running for over a century, we'd better kill it! wrote in message news:b9193c83-2128-40b6-8023-67fac4611e91@.discussions.microsoft.com... >I was trying to findout processes which are running for long time and
> came across few entries in sysprocess where Last_Batch column shows
> '01/01/1900' >
> It is a usual practice for SQL coders to put a default date like this if
> they don't have a valid date, does sysprocesses table does the same
> thing ?[:(]
>

query on sysprocesses...

I was trying to findout processes which are running for long time and came across few entries in sysprocess where Last_Batch column shows '01/01/1900'

It is a usual practice for SQL coders to put a default date like this if they don't have a valid date, does sysprocesses table does the same thing ?Hi

It is not the best, but this table dates back to when SQL Server was produced by Sybase. A long history of backward-compatability. Microsoft has changed the system tables in SQL Server 2005 (sys.dm_exec_sessions in this case)

Regards
Mike|||No, this is not the way I would have done it. This is a token date, which

more or less means the engine is saying, I don't have information about when

this process started. I would rather see NULL here, though the engine

should be able to figure out when it first saw this process (?). I can see

novice DBAs reaching for the panic button, hey, this process has been

running for over a century, we'd better kill it!

wrote in message

news:b9193c83-2128-40b6-8023-67fac4611e91@.discussions.microsoft.com...

>I was trying to findout processes which are running for long time and

> came across few entries in sysprocess where Last_Batch column shows

> '01/01/1900'

>

> It is a usual practice for SQL coders to put a default date like this if

> they don't have a valid date, does sysprocesses table does the same

> thing ?[:(]

>

Query on large record

First thing I am new to write a query. I have two tables that I need
information from. The first table has the user, date and time. The second
table has the user, date, time and a record that contains 132 characters.
What I need to do is match the user, Date and time along with 7 characters
that are placed 7 positions in the record.
This is the begining of the record looks like and I only need the 0685043,
is this possible?
OVRIDE 0685043
Thanks in advance for any help.
I don't know what you're matching the 0685043 with, but
you will probably need the SUBSTRING function, which you
can learn about from Books Online, to extract that from the
rest of the 132 character string.
Generally, if substrings of a column have meaning of their own,
it is better to keep that information in a separate column of the
table.
Steve Kass
Drew University
Daniell wrote:

>First thing I am new to write a query. I have two tables that I need
>information from. The first table has the user, date and time. The second
>table has the user, date, time and a record that contains 132 characters.
>What I need to do is match the user, Date and time along with 7 characters
>that are placed 7 positions in the record.
>This is the begining of the record looks like and I only need the 0685043,
>is this possible?
>OVRIDE 0685043
>Thanks in advance for any help.
>
>
|||Thanks Steve I guess I should have explained a little better. I will give
the SUBSTRING a try.
"Steve Kass" wrote:

> I don't know what you're matching the 0685043 with, but
> you will probably need the SUBSTRING function, which you
> can learn about from Books Online, to extract that from the
> rest of the 132 character string.
> Generally, if substrings of a column have meaning of their own,
> it is better to keep that information in a separate column of the
> table.
> Steve Kass
> Drew University
> Daniell wrote:
>

Query on date/time data type

I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
I've been trying CONVERT and CAST but to no avail. There's no examples on
how to utilize the TIME part of this smalldatetime data type.
Thx,
Don
SQL2000
WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
AND MyDateTimeColumn <= '2006-10-24 10:55:00'
)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the top yourself.
- H. Norman Schwarzkopf
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
>I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
> Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
> I've been trying CONVERT and CAST but to no avail. There's no examples on
> how to utilize the TIME part of this smalldatetime data type.
> Thx,
> Don
> SQL2000
>
|||DOH! )
I was trying to make it too compliated!
Thanks!
Don
"Arnie Rowland" wrote:
[vbcol=seagreen]
> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
> )
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
|||I recommend that you use either of below two formats for the datetime strings:
'20061024 01:00:00'
'2006-10-24T01:00:00'
If you use any other format, the app will be language dependent
(http://www.karaszi.com/SQLServer/inf...asp#DtFormats). as shown below:
SET LANGUAGE german
SELECT CAST('2006-10-24 01:00:00' AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:98D78D54-EFF9-4097-BBD5-80D338D2663F@.microsoft.com...[vbcol=seagreen]
> DOH! )
> I was trying to make it too compliated!
> Thanks!
> Don
>
> "Arnie Rowland" wrote:

Query on date/time data type

I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
I've been trying CONVERT and CAST but to no avail. There's no examples on
how to utilize the TIME part of this smalldatetime data type.
Thx,
Don
SQL2000WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
AND MyDateTimeColumn <= '2006-10-24 10:55:00'
)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D
40-8B10-5D6AEB90CDCE@.microsoft.com...
>I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
>
> Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
>
> I've been trying CONVERT and CAST but to no avail. There's no examples on
> how to utilize the TIME part of this smalldatetime data type.
>
> Thx,
> Don
> SQL2000
>
>|||DOH! )
I was trying to make it too compliated!
Thanks!
Don
"Arnie Rowland" wrote:
[vbcol=seagreen]
> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
> )
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e top yourself.
> - H. Norman Schwarzkopf
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message new
s:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...|||I recommend that you use either of below two formats for the datetime string
s:
'20061024 01:00:00'
'2006-10-24T01:00:00'
If you use any other format, the app will be language dependent
(http://www.karaszi.com/SQLServer/in...e.asp#DtFormats). as shown bel
ow:
SET LANGUAGE german
SELECT CAST('2006-10-24 01:00:00' AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:98D78D54-EFF9-4097-BBD5-80D338D2663F@.microsoft.com...[vbcol=seagreen]
> DOH! )
> I was trying to make it too compliated!
> Thanks!
> Don
>
> "Arnie Rowland" wrote:
>

Query on date/time data type

I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
I've been trying CONVERT and CAST but to no avail. There's no examples on
how to utilize the TIME part of this smalldatetime data type.
Thx,
Don
SQL2000This is a multi-part message in MIME format.
--=_NextPart_000_06CE_01C6F760.251370F0
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
WHERE ( MyDateTimeColumn >=3D '2006-10-24 01:00:00' AND MyDateTimeColumn <=3D '2006-10-24 10:55:00'
)
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message =news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
>I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
> > Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
> > I've been trying CONVERT and CAST but to no avail. There's no examples =on > how to utilize the TIME part of this smalldatetime data type.
> > Thx,
> Don
> SQL2000
> >
--=_NextPart_000_06CE_01C6F760.251370F0
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

WHERE ( =MyDateTimeColumn >=3D '2006-10-24 01:00:00'
AND MyDateTimeColumn <=3D '2006-10-24 10:55:00'
)
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"donsql22222" wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...>I =need everthing from 1AM to 10:55AM. Can't figure out the syntax.> > Need =from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"> > I've =been trying CONVERT and CAST but to no avail. There's no examples on > how to =utilize the TIME part of this smalldatetime data type.> > =Thx,> Don> SQL2000> >

---

--=_NextPart_000_06CE_01C6F760.251370F0--|||DOH! :))
I was trying to make it too compliated!
Thanks!
Don
"Arnie Rowland" wrote:
> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
> )
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the top yourself.
> - H. Norman Schwarzkopf
>
> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
> >I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
> >
> > Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
> >
> > I've been trying CONVERT and CAST but to no avail. There's no examples on
> > how to utilize the TIME part of this smalldatetime data type.
> >
> > Thx,
> > Don
> > SQL2000
> >
> >|||I recommend that you use either of below two formats for the datetime strings:
'20061024 01:00:00'
'2006-10-24T01:00:00'
If you use any other format, the app will be language dependent
(http://www.karaszi.com/SQLServer/info_datetime.asp#DtFormats). as shown below:
SET LANGUAGE german
SELECT CAST('2006-10-24 01:00:00' AS datetime)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
news:98D78D54-EFF9-4097-BBD5-80D338D2663F@.microsoft.com...
> DOH! :))
> I was trying to make it too compliated!
> Thanks!
> Don
>
> "Arnie Rowland" wrote:
>> WHERE ( MyDateTimeColumn >= '2006-10-24 01:00:00'
>> AND MyDateTimeColumn <= '2006-10-24 10:55:00'
>> )
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the top yourself.
>> - H. Norman Schwarzkopf
>>
>> "donsql22222" <donsql22222@.discussions.microsoft.com> wrote in message
>> news:D64028F9-5CA6-4D40-8B10-5D6AEB90CDCE@.microsoft.com...
>> >I need everthing from 1AM to 10:55AM. Can't figure out the syntax.
>> >
>> > Need from "2006-10-24 01:00:00" to "2006-10-24 10:55:00"
>> >
>> > I've been trying CONVERT and CAST but to no avail. There's no examples on
>> > how to utilize the TIME part of this smalldatetime data type.
>> >
>> > Thx,
>> > Don
>> > SQL2000
>> >
>> >

Query Merging/ Query Transformation ??

Hello,

Can someone plz refer/recommend any document on query merging? I am working on a database sever. The response time, of view's query has become a challange to me. I have tried everything, the last hope left is query merging.
But I didnt find any docs/papers/books on it.

Plz help.
Shigs.
=============================
Are there those,
In this world of brave,
Who can tell me,
How should I behave,
When I am disgraced.
=============================Could you please explain on the term Query merging.

If you want to merge certain table in the query you can use JOINs, refer to books online for more information.|||Could you please explain on the term Query merging.

If you want to merge certain table in the query you can use JOINs, refer to books online for more information.|||Hi Satya

Thanks a lot. Query Merging that I referred was not related to merging of one table's data into another, but if I fire a query, which is containing a subquery in it, then the execution may take two different strategies, 1. Execute the subquery, fetch the result and compare this data with the parent querie's data. 2. Merge the subquery into Parent query and execute them as if the query wasn't subquery but a single level '0' query.

The best xample is Oracle's COMPLEX_QUERY_MERGING option. I want to understand, how Oarcle internally merges these queries? Is there any Optimizer Doc, which gives the detail?

Waiting...|||Is your question in regards to Oracle? This is a SQL Server forum, and SQL Server generally does a good job of optimizing queries, even with complex embedded subqueries.
If you are having slow response times on SQL Server, then post your query and someone on the forum might be able to help you optimize it.|||You can check the execution plan of that query in query analyzer which gives you better idea about the table scans and performance.|||I have a question...are there fewer and fewer Oracle projects out there these days?|||One can only hope...