How To Optimize Performance In Views.

May 30, 2008

Hi guys,

I'm have a table which is filled daily by a procedure. This procedure executes for around 2 hours.
Now i also want to create a clustered index view on this table.

Now during the time data is being inserted in this table, would the performance of server get hit.?
Since it think it would continiously be updating this view during this duration.

Is there a way i can temporarily disconnect the view?

Please advice.

Thanks.

View 5 Replies

How Can You Optimize Duplicated Work In Views?

Jul 20, 2005

This one's kind of hard to explain, so I've opted to post a simplifiedversion of our view that prompted me to ask this question: Thequestion is re-asked after the view...create view MainView (PrimaryKeyID,SubTotal1,SubTotal2,GrandTotal)asselect t.PrimaryKeyID,sum(t1.Total),sum(t2.Total),sum(t1.Total) + sum(t2.Total)from SomeTable tjoin CalculationTable t1 on ...join AnotherCalculationTable t2 on ...Notice in the 3rd column called "GrandTotal" how it calls the function"sum" two more times. Common sense tells me that this is notnecessary. in our case it's orders of magnitude worse... Is the queryoptimizer smart enough to only call these sums once per row in"SomeTable"? Common sense tells me that if we were to break the viewsapart into two views it would avoid this ineffeciency:create view InnerView (PrimaryKeyID,SubTotal1,SubTotal2)asselect t.PrimaryKeyID,sum(t1.Total),sum(t2.Total)from SomeTable tjoin CalculationTable t1 on ...join AnotherCalculationTable t2 on ...create view OuterView (PrimaryKeyID,SubTotal1,SutTotal2,GrandTotal)asselect iv.PrimaryKeyID,iv.Total1,iv.Total2,iv.Total1 + iv.Total2from InnerViewNotice how it appears that we've tricked the optimizer into thinkingthere are less operations. So my question is how does views handlethis situation? Does the optimizer treat both version the same? Or isone faster than the other? Or is there another, faster way? Doesadding levels of views slow down things, or are views simply likemacros and get removed when compiled (I think I've read the latter istrue actually)Thanks,Dave

View 1 Replies View Related

Optimize Access To SQL Server Performance

Dec 27, 1999

How can you optimize Access queries on linked SQL Server tables?

View 1 Replies View Related

Performance Of Views

Nov 10, 2005

I'm currently using a system where the number of column in any given table is so great that the columns are often split into additonal tables. I know it's a wierd design but there you go. So I have to deal with tables looking like: MathResult, MathResult_2, MathResult_3, etcEach table is basically the same entity, i.e. it has the same number of rows and each row has the same key value as its peer tables. My question is that should I create a view to bring the tables together, given that a View doesn't seem to have any sort of row-size restriction? Normally I shy away from Views because I've always found them to bring performance down. Any thoughts?

View 4 Replies View Related

Views And Performance

Apr 17, 2000

Hello everyone,

I want to use VIEWS to filter records using a stored procedure to call the VIEW. Will I get a performance boost from using a VIEW, or should I just use the SQL statement that I used to create the VIEW, in my SP and forget about calling a VIEW at all?

Thanks,
Nathan

View 1 Replies View Related

Performance Tuning On Views?

Aug 27, 2014

I have a query which retrieves data from 4-5 tables. To restrict the acess directly to the tables, views have been created on all these tables. These views are just select * from the tables. Two of these 5 tables have 700 Million and 8 Million rows respectively. And all the tables are having indexes. My issue here is that my query on views take three times more then the duration it takes to retrive data directly from tables. e.g. To retrieve 1 Miliion rows, it takes just 7-10 minutes on tables but on views it takes more than 30 minutes. When I check the query plan for both the options, I can see that indexes are being picked up but still the views are very slow.

Creating indexes on views is not feasible option for me as it requires DDL changes and so much testing efforts.

View 14 Replies View Related

Do Views Slow Down Query Performance?

Oct 22, 2004

When working with databases containing myriad of huge tables, I am very much tempted to create categorized views on those tables in order to simplify and facilitate data query programming? Some developers I talk to say such views generally slow down query performance. Is this true?
Thanks.

View 1 Replies View Related

Joining Views && Query Performance

May 1, 2006

Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.

A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.

Thanks, Dave

View 6 Replies View Related

EAV Performance And Clustered Indexes On Views

Jan 25, 2008

OK so I have this EAV system on a server that is old enough for kindergarten. Insanely enough, this company that makes more money than any of your gods can not buy me a new box.

Before you say "redesign", I need funding allocated for that. See my first statement.

Anywho, I have this page that touches the dreaded Value table and does a clustered index seek on it. Can't search faster than that, right? Well I am getting some funding for "performance tuning". I am wondering if maybe incorporating some clustered index views involving the value table and producing a smaller clustered index for it to seek may alleviate some of this. Any thoughts?

View 10 Replies View Related

Ways To Improve Views Performance

Jan 19, 2008

Dear All,
i've tried with indexed views, but because the view is referenceing another view, i was unable to create a clustered index on that view.
so please let me know how can i improve the performance of the view.

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 1 Replies View Related

Performance Problem With Views And Stored Procedures..

Nov 8, 2000

Hello Everybody,

I posted this same question couple of times in the news groups but no answers. I have a 2 tables and i am doing a union query using a view. each has 250 rows. The query takes 20 seconds to return the results. no joins or anything. the create view simply looks like this:

create view myview as
select id, name from table1
union
select id,name from table2

Where as if i write a stored procedure like below, it returns the rows in 4 seconds.
create table #mytable
( id int, name varchar(30))
insert into #mytable (id, name) select id, name from table1
insert into #mytable (id, name) select id, name from table2
select id,name from #mytable.

I prefer doing in the view since both returns the same result. I tried running dbcc, update statistics. but no luck. Can anyone please help me in this issue.

Thanks
Ramesh

View 3 Replies View Related

Performance Of Views Accessed By Multiple SPIDs?

Jul 23, 2005

I wonder if anyone has any hard fact based pro or contra, especially onperformance, about having views as opposed to tables when the object isbeing accessed for read only by multiple sessions/users/spids. I amparticularly concerned about the multiple instantiations of the view.Relevant thoughts on this are much appreciated.Thanks,Caveman

View 1 Replies View Related

Poor Performance - Nested Views && Complex Joins

Sep 27, 2006

The code below is from a nested view, which I've read should be avoided. I've also noticed GETDATE() is used, which I believe causes GETDATE() to be executed for every record selected (correct me if I'm wrong). I'm also guessing a JOIN containing a UNION against a SELECT statement is not a good idea. What other problems do you notice?

SELECT trans.Entry_Code, trans.D_C, trans.ADP_Security_# ,
trans.TRID, trans.Batch_Code, trans.Last_Money,
null as Shares, Settle_date as Process_Date,
null as Closing_Price, trans.Dwnld_Date, trans.Acnt,
null as Mktval,
cast(Null as varchar(20)) as Cusip_#,
ACT.dbo.account.account_key AS account_key
FROM (SELECT * FROM ADPDBBOOK.dbo.YTD05B
WHERE (DATEDIFF(mm, Process_Date, GETDATE()) <= 15)
UNION
SELECT * FROM ADPDBBOOK.dbo.YTD06B) trans
INNER JOIN ACT_DATA.dbo.account
ON ACT_DATA.dbo.account.account_key = RIGHT(trans.Acnt, 5)
INNER JOIN tbl_Accounts_TransactionalData
ON trans.Acnt = tbl_Accounts_TransactionalData.Acnt

Thanks, Dave

View 9 Replies View Related

Query Performance On Paritioned Views With Check Constraints

Mar 21, 2007

Hi,

I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.

I have two partitioned tables and a view on top of both tables as below:
create table [dbo].[Table_1]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO

create table [dbo].[Table_2]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO
create view TableView
as
select * from Table_1
union all
select * from Table_2
GO

Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.

I then ran the query execution plan for the following query on both SQL server 2000 and 2005.
select * from TableView where TableID = 10

On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.

Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below:
create table [dbo].[Table_1]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK ([TableID] BETWEEN 1 AND 999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO

create table [dbo].[Table_2]
(
[TableID] INTEGER PRIMARY KEY NONCLUSTERED
CHECK ([TableID] BETWEEN 1000 AND 1999),
[AnyOtherColumn] int NOT NULL ,
) ON [Primary]
GO

create view TableView
as
select * from Table_1
union all
select * from Table_2
GO

Now run the query execution plan for the same query again.

select * from TableView where TableID = 10

This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.

I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?

Is it a bug in SQL server?

Or am I missing any thing?

Any help appreciated.

Thanks

View 2 Replies View Related

Are Embedded Views (Views Within Views...) Evil And If So Why?

Apr 3, 2006

Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.

View 15 Replies View Related

Large Views Vs Multiple Small Views

Sep 6, 2007

Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?

View 1 Replies View Related

How To Optimize This?

Jan 27, 2008

Hi.I have a sql: 1 SELECT ForumID, Title, Description,2 (SELECT PostID3 FROM (SELECT TOP (1) PostID4 FROM Forum_Posts5 WHERE (ForumID = Forum_Forums.ForumID)6 ORDER BY PostedDate DESC) AS derivedtbl_1) AS LastPostID,7
8
9 (SELECT Title10 FROM (SELECT TOP (1) Title11 FROM Forum_Posts12 WHERE (ForumID = Forum_Forums.ForumID)13 ORDER BY PostedDate DESC) AS derivedtbl_2) AS LastPostTitle,14
15 (SELECT PostedBy16 FROM (SELECT TOP (1) PostedBy17 FROM Forum_Posts18 WHERE (ForumID = Forum_Forums.ForumID)19 ORDER BY PostedDate DESC) AS derivedtbl_3) AS PostedBy20 FROM Forum_Forums21 WHERE (BoardID = @BoardID)I must use this code 3 times: SELECT TOP (1) PostID / Title / PostedBy FROM Forum_Posts WHERE (ForumID = Forum_Forums.ForumID)ORDER BY PostedDate DESCPlease help me optimize this.

View 4 Replies View Related

Plz Help To Optimize.

Jul 3, 2006

A table fin_cutoff contains following type of data.

fin_beg_dt | fin_end_dt
------------------------------------------------

2005-12-01 00:00:00.000 | 2005-12-31 00:00:00.000
2005-10-29 00:00:00.000 | 2005-12-01 00:00:00.000
2005-10-01 00:00:00.000 | 2005-10-29 00:00:00.000

I want to extract a row where the fin_beg_dt & fin_end_dt columns entries belong to current month

Following query is written for the same:

select fin_beg_dt,
fin_end_dt
from ecr_fin_cutoff
where month(fin_beg_dt_id) = month(getdate())
andyear(fin_dt_id)= year(getdate())
and month(fin_end_dt) = month(getdate())
and year(fin_end_dt)= year(getdate())

It is giving the appropriate results.
Please help me to optimize it.

Thanks in advance.

Shrirang

View 1 Replies View Related

How To Optimize The Sql Below?

Nov 8, 2006

I have a sql such sa below:

select
coalesce(T1.LocalNetID, T2.LocalNetID)as LocalNetID,coalesce(T1.BSCID, T2.BSCID)as BSCID,coalesce(T1.SiteID, T2.SiteID)as SiteID, coalesce(T1.CellID, T2.CellID)as CellID,
coalesce(T1.StrTime, T2.StrTime) as StrTime,
C10701 as '10701',C11601 as '11601'from
(
--Start for Opt_Cell_pBasicM of inner table--
(
select
LocalNetID,BSCID,SiteID, CellID,
StrTime,RecordDataTime,
C11601
from
Opt_Cell_pBasicM
where
((LocalNetID = 13 and BSCID = 2))
and
((RecordDataTime between '2006-01-01 00:01:00' and '2006-01-01 12:00:00'))
and
(GranularityPeriod <= 12)
)
--End for Opt_Cell_pBasicM of inner table--
as T1
full outer join
--Start for Opt_Cell_pCellRadioM of inner table--
(
select
LocalNetID,BSCID, SiteID, CellID,
StrTime,RecordDataTime,
C10701
from
Opt_Cell_pCellRadioM
where
(
((LocalNetID = 13 and BSCID = 2))
and
((RecordDataTime between '2006-01-01 00:01:00' and '2006-01-01 12:00:00'))
and
(GranularityPeriod <= 12)
)
)
--End for Opt_Cell_pCellRadioM of inner table--
as T2
on coalesce(T1.LocalNetID,NULL) = T2.LocalNetID and coalesce(T1.BSCID,NULL) = T2.BSCID and coalesce(T1.SiteID,NULL) = T2.SiteID and coalesce(T1.CellID,NULL) = T2.CellID
and coalesce(T1.RecordDataTime,NULL) = T2.RecordDataTime
--End for first join--
)

when num of the records of Opt_Cell_pBasicM and Opt_Cell_pCellRadioM
is greater than 1000000, the speed is very slow , how to optimize it ?

thks

View 3 Replies View Related

My God, Who Can Help Me Optimize The Sql?

Nov 16, 2006

the sql of create table is below:

CREATE TABLE [dbo].[Examp] (
[LocalNetID] [int] NOT NULL ,
[BSCID] [int] NOT NULL ,
[SiteID] [int] NOT NULL ,
[CellID] [int] NOT NULL ,
[StrTime] [varchar] (30) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[M1] [int] NULL ,
[M2] [int] NULL ,
[M3] [int] NULL ,
[M4] [int] NULL ,
[M5] [int] NULL ,
[M6] [int] NULL ,
[M7] [int] NULL ,
[M8] [int] NULL ,
[M9] [int] NULL ,
[M10] [int] NULL ,
[M11] [int] NULL ,
[M12] [int] NULL ,
[M13] [int] NULL ,
[M14] [int] NULL ,
[M15] [int] NULL ,
[M16] [int] NULL ,
[M17] [int] NULL ,
[M18] [int] NULL ,
[M19] [int] NULL ,
[M20] [int] NULL ,
[M21] [int] NULL ,
[M22] [int] NULL ,
[M23] [int] NULL ,
[M24] [int] NULL ,
[M25] [int] NULL ,
[M26] [int] NULL ,
[M27] [int] NULL ,
[M28] [int] NULL ,
[M29] [int] NULL ,
[M30] [int] NULL ,
[M31] [int] NULL ,
[M32] [int] NULL ,
[M33] [int] NULL ,
[M34] [int] NULL ,
[M35] [int] NULL ,
[M36] [int] NULL ,
[M37] [int] NULL ,
[M38] [int] NULL ,
[M39] [int] NULL ,
[M40] [int] NULL ,
[M41] [int] NULL ,
[M42] [int] NULL ,
[M43] [int] NULL ,
[M44] [int] NULL ,
[M45] [int] NULL ,
[M46] [int] NULL ,
[M47] [int] NULL ,
[M48] [int] NULL ,
[M49] [int] NULL ,
[M50] [int] NULL ,
[M51] [int] NULL ,
[M52] [int] NULL ,
[M53] [int] NULL ,
[M54] [int] NULL ,
[M55] [int] NULL ,
[M56] [int] NULL ,
[M57] [int] NULL ,
[M58] [int] NULL ,
[M59] [int] NULL ,
[M60] [int] NULL ,
[M61] [int] NULL ,
[M62] [int] NULL ,
[M63] [int] NULL ,
[M64] [int] NULL ,
[M65] [int] NULL ,
[M66] [int] NULL ,
[M67] [int] NULL ,
[M68] [int] NULL ,
[M69] [int] NULL ,
[M70] [int] NULL ,
[M71] [int] NULL ,
[M72] [int] NULL ,
[M73] [int] NULL ,
[M74] [int] NULL ,
[M75] [int] NULL ,
[M76] [int] NULL ,
[M77] [int] NULL ,
[M78] [int] NULL ,
[M79] [int] NULL ,
[M80] [int] NULL ,
[M81] [int] NULL ,
[M82] [int] NULL ,
[M83] [int] NULL ,
[M84] [int] NULL ,
[M85] [int] NULL ,
[M86] [int] NULL ,
[M87] [int] NULL ,
[M88] [int] NULL ,
[M89] [int] NULL ,
[M90] [int] NULL ,
[M91] [int] NULL ,
[M92] [int] NULL ,
[M93] [int] NULL ,
[M94] [int] NULL ,
[M95] [int] NULL ,
[M96] [int] NULL ,
[M97] [int] NULL ,
[M98] [int] NULL ,
[M99] [int] NULL ,
[M100] [int] NULL

)

and LocalNetID, BSCID, SiteID, CellID, StrTime are set index,

the sql i want to query is :

select
LocalNetID, BSCID, SiteID, CellID, StrTime,
Sum(M1) as M1,Sum(M2) as M2,Sum(M3) as M3,Sum(M4) as M4,Sum(M5) as M5,Sum(M6) as M6,Sum(M7) as M7,Sum(M8) as M8,Sum(M9) as M9,Sum(M10) as M10,Sum(M11) as M11,Sum(M12) as M12,Sum(M13) as M13,Sum(M14) as M14,Sum(M15) as M15,Sum(M16) as M16,Sum(M17) as M17,Sum(M18) as M18,Sum(M19) as M19,Sum(M20) as M20,Sum(M21) as M21,Sum(M22) as M22,Sum(M23) as M23,Sum(M24) as M24,Sum(M25) as M25,Sum(M26) as M26,Sum(M27) as M27,Sum(M28) as M28,Sum(M29) as M29,Sum(M30) as M30,Sum(M31) as M31,Sum(M32) as M32,Sum(M33) as M33,Sum(M34) as M34,Sum(M35) as M35,Sum(M36) as M36,Sum(M37) as M37,Sum(M38) as M38,Sum(M39) as M39,Sum(M40) as M40,Sum(M41) as M41,Sum(M42) as M42,Sum(M43) as M43,Sum(M44) as M44,Sum(M45) as M45,Sum(M46) as M46,Sum(M47) as M47,Sum(M48) as M48,Sum(M49) as M49,Sum(M50) as M50,Sum(M51) as M51,Sum(M52) as M52,Sum(M53) as M53,Sum(M54) as M54,Sum(M55) as M55,Sum(M56) as M56,Sum(M57) as M57,Sum(M58) as M58,Sum(M59) as M59,Sum(M60) as M60,Sum(M61) as M61,Sum(M62) as M62,Sum(M63) as M63,Sum(M64) as M64,Sum(M65) as M65,Sum(M66) as M66,Sum(M67) as M67,Sum(M68) as M68,Sum(M69) as M69,Sum(M70) as M70,Sum(M71) as M71,Sum(M72) as M72,Sum(M73) as M73,Sum(M74) as M74,Sum(M75) as M75,Sum(M76) as M76,Sum(M77) as M77,Sum(M78) as M78,Sum(M79) as M79,Sum(M80) as M80,Sum(M81) as M81,Sum(M82) as M82,Sum(M83) as M83,Sum(M84) as M84,Sum(M85) as M85,Sum(M86) as M86,Sum(M87) as M87,Sum(M88) as M88,Sum(M89) as M89,Sum(M90) as M90,Sum(M91) as M91,Sum(M92) as M92,Sum(M93) as M93,Sum(M94) as M94,Sum(M95) as M95,Sum(M96) as M96,Sum(M97) as M97,Sum(M98) as M98,Sum(M99) as M99,Sum(M100) as M100
from
(
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
Sum(M1) as M1,Sum(M2) as M2,Sum(M3) as M3,Sum(M4) as M4,Sum(M5) as M5,Sum(M6) as M6,Sum(M7) as M7,Sum(M8) as M8,Sum(M9) as M9,Sum(M10) as M10,Sum(M11) as M11,Sum(M12) as M12,Sum(M13) as M13,Sum(M14) as M14,Sum(M15) as M15,Sum(M16) as M16,Sum(M17) as M17,Sum(M18) as M18,Sum(M19) as M19,Sum(M20) as M20,Sum(M21) as M21,Sum(M22) as M22,Sum(M23) as M23,Sum(M24) as M24,Sum(M25) as M25,Sum(M26) as M26,Sum(M27) as M27,Sum(M28) as M28,Sum(M29) as M29,Sum(M30) as M30,Sum(M31) as M31,Sum(M32) as M32,Sum(M33) as M33,Sum(M34) as M34,Sum(M35) as M35,Sum(M36) as M36,Sum(M37) as M37,Sum(M38) as M38,Sum(M39) as M39,Sum(M40) as M40,Sum(M41) as M41,Sum(M42) as M42,Sum(M43) as M43,Sum(M44) as M44,Sum(M45) as M45,Sum(M46) as M46,Sum(M47) as M47,Sum(M48) as M48,Sum(M49) as M49,Sum(M50) as M50,Sum(M51) as M51,Sum(M52) as M52,Sum(M53) as M53,Sum(M54) as M54,Sum(M55) as M55,Sum(M56) as M56,Sum(M57) as M57,Sum(M58) as M58,Sum(M59) as M59,Sum(M60) as M60,Sum(M61) as M61,Sum(M62) as M62,Sum(M63) as M63,Sum(M64) as M64,Sum(M65) as M65,Sum(M66) as M66,Sum(M67) as M67,Sum(M68) as M68,Sum(M69) as M69,Sum(M70) as M70,Sum(M71) as M71,Sum(M72) as M72,Sum(M73) as M73,Sum(M74) as M74,Sum(M75) as M75,Sum(M76) as M76,Sum(M77) as M77,Sum(M78) as M78,Sum(M79) as M79,Sum(M80) as M80,Sum(M81) as M81,Sum(M82) as M82,Sum(M83) as M83,Sum(M84) as M84,Sum(M85) as M85,Sum(M86) as M86,Sum(M87) as M87,Sum(M88) as M88,Sum(M89) as M89,Sum(M90) as M90,Sum(M91) as M91,Sum(M92) as M92,Sum(M93) as M93,Sum(M94) as M94,Sum(M95) as M95,Sum(M96) as M96,Sum(M97) as M97,Sum(M98) as M98,Sum(M99) as M99,Sum(M100) as M100
from
(
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
Sum(M1) as M1,Sum(M2) as M2,Sum(M3) as M3,Sum(M4) as M4,Sum(M5) as M5,Sum(M6) as M6,Sum(M7) as M7,Sum(M8) as M8,Sum(M9) as M9,Sum(M10) as M10,Sum(M11) as M11,Sum(M12) as M12,Sum(M13) as M13,Sum(M14) as M14,Sum(M15) as M15,Sum(M16) as M16,Sum(M17) as M17,Sum(M18) as M18,Sum(M19) as M19,Sum(M20) as M20,Sum(M21) as M21,Sum(M22) as M22,Sum(M23) as M23,Sum(M24) as M24,Sum(M25) as M25,Sum(M26) as M26,Sum(M27) as M27,Sum(M28) as M28,Sum(M29) as M29,Sum(M30) as M30,Sum(M31) as M31,Sum(M32) as M32,Sum(M33) as M33,Sum(M34) as M34,Sum(M35) as M35,Sum(M36) as M36,Sum(M37) as M37,Sum(M38) as M38,Sum(M39) as M39,Sum(M40) as M40,Sum(M41) as M41,Sum(M42) as M42,Sum(M43) as M43,Sum(M44) as M44,Sum(M45) as M45,Sum(M46) as M46,Sum(M47) as M47,Sum(M48) as M48,Sum(M49) as M49,Sum(M50) as M50,Sum(M51) as M51,Sum(M52) as M52,Sum(M53) as M53,Sum(M54) as M54,Sum(M55) as M55,Sum(M56) as M56,Sum(M57) as M57,Sum(M58) as M58,Sum(M59) as M59,Sum(M60) as M60,Sum(M61) as M61,Sum(M62) as M62,Sum(M63) as M63,Sum(M64) as M64,Sum(M65) as M65,Sum(M66) as M66,Sum(M67) as M67,Sum(M68) as M68,Sum(M69) as M69,Sum(M70) as M70,Sum(M71) as M71,Sum(M72) as M72,Sum(M73) as M73,Sum(M74) as M74,Sum(M75) as M75,Sum(M76) as M76,Sum(M77) as M77,Sum(M78) as M78,Sum(M79) as M79,Sum(M80) as M80,Sum(M81) as M81,Sum(M82) as M82,Sum(M83) as M83,Sum(M84) as M84,Sum(M85) as M85,Sum(M86) as M86,Sum(M87) as M87,Sum(M88) as M88,Sum(M89) as M89,Sum(M90) as M90,Sum(M91) as M91,Sum(M92) as M92,Sum(M93) as M93,Sum(M94) as M94,Sum(M95) as M95,Sum(M96) as M96,Sum(M97) as M97,Sum(M98) as M98,Sum(M99) as M99,Sum(M100) as M100
from
(
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
Sum(M1) as M1,Sum(M2) as M2,Sum(M3) as M3,Sum(M4) as M4,Sum(M5) as M5,Sum(M6) as M6,Sum(M7) as M7,Sum(M8) as M8,Sum(M9) as M9,Sum(M10) as M10,Sum(M11) as M11,Sum(M12) as M12,Sum(M13) as M13,Sum(M14) as M14,Sum(M15) as M15,Sum(M16) as M16,Sum(M17) as M17,Sum(M18) as M18,Sum(M19) as M19,Sum(M20) as M20,Sum(M21) as M21,Sum(M22) as M22,Sum(M23) as M23,Sum(M24) as M24,Sum(M25) as M25,Sum(M26) as M26,Sum(M27) as M27,Sum(M28) as M28,Sum(M29) as M29,Sum(M30) as M30,Sum(M31) as M31,Sum(M32) as M32,Sum(M33) as M33,Sum(M34) as M34,Sum(M35) as M35,Sum(M36) as M36,Sum(M37) as M37,Sum(M38) as M38,Sum(M39) as M39,Sum(M40) as M40,Sum(M41) as M41,Sum(M42) as M42,Sum(M43) as M43,Sum(M44) as M44,Sum(M45) as M45,Sum(M46) as M46,Sum(M47) as M47,Sum(M48) as M48,Sum(M49) as M49,Sum(M50) as M50,Sum(M51) as M51,Sum(M52) as M52,Sum(M53) as M53,Sum(M54) as M54,Sum(M55) as M55,Sum(M56) as M56,Sum(M57) as M57,Sum(M58) as M58,Sum(M59) as M59,Sum(M60) as M60,Sum(M61) as M61,Sum(M62) as M62,Sum(M63) as M63,Sum(M64) as M64,Sum(M65) as M65,Sum(M66) as M66,Sum(M67) as M67,Sum(M68) as M68,Sum(M69) as M69,Sum(M70) as M70,Sum(M71) as M71,Sum(M72) as M72,Sum(M73) as M73,Sum(M74) as M74,Sum(M75) as M75,Sum(M76) as M76,Sum(M77) as M77,Sum(M78) as M78,Sum(M79) as M79,Sum(M80) as M80,Sum(M81) as M81,Sum(M82) as M82,Sum(M83) as M83,Sum(M84) as M84,Sum(M85) as M85,Sum(M86) as M86,Sum(M87) as M87,Sum(M88) as M88,Sum(M89) as M89,Sum(M90) as M90,Sum(M91) as M91,Sum(M92) as M92,Sum(M93) as M93,Sum(M94) as M94,Sum(M95) as M95,Sum(M96) as M96,Sum(M97) as M97,Sum(M98) as M98,Sum(M99) as M99,Sum(M100) as M100
from
(
select
LocalNetID, BSCID, SiteID, CellID, StrTime,
M1 , M2 , M3 , M4 , M5 , M6 , M7 , M8 , M9 , M10 ,M11 , M12 , M13 , M14 , M15 , M16 , M17 , M18 , M19 , M20 ,M21 , M22 , M23 , M24 , M25 , M26 , M27 , M28 , M29 , M30 ,M31 , M32 , M33 , M34 , M35 , M36 , M37 , M38 , M39 , M40 ,M41 , M42 , M43 , M44 , M45 , M46 , M47 , M48 , M49 , M50 ,M51 , M52 , M53 , M54 , M55 , M56 , M57 , M58 , M59 , M60 ,M61 , M62 , M63 , M64 , M65 , M66 , M67 , M68 , M69 , M70 ,M71 , M72 , M73 , M74 , M75 , M76 , M77 , M78 , M79 , M80 ,M81 , M82 , M83 , M84 , M85 , M86 , M87 , M88 , M89 , M90 ,M91 , M92 , M93 , M94 , M95 , M96 , M97 , M98 , M99 , M100
from examp
) as T1
group by
LocalNetID, BSCID, SiteID, CellID, StrTime
) as T2
group by
LocalNetID, BSCID, SiteID, CellID, StrTime
) as T3
group by
LocalNetID, BSCID, SiteID, CellID, StrTime
) as T4
group by
LocalNetID, BSCID, SiteID, CellID, StrTime

when there is no data in table, it will take more than 3 minutes on my computer!

how can i optimize the sql to speed it?

thks

View 2 Replies View Related

Optimize LIKE

Feb 6, 2007

It's bad enough that SQL CE 2.0 doesn't support views, when I use "LIKE" in a SELECT statement (eg., SELECT ... WHERE fldname LIKE '%word%'), the response is terrible to the point that it doesn't come back. In my case, the 'word' can be anywhere in 'fldname'.

How do you optimize the LIKE operator? I created an index on the field but it didn't make a bit of difference.

Thank you.

View 1 Replies View Related

Recompiling Views That Reference Other Views

Jun 28, 2007

Hello.

Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.

We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.

Recently we made changes to the secondary view.

After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.

Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.

My questions are:

1. Exactly why did this happen and is there a proper name for it when it does?

2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?

Thanks for any help on the matter. I greatly appreciate it.

View 3 Replies View Related

HELP ME TO OPTIMIZE QUERY

Feb 2, 2007

Hello friends,

I’m facing performance related problem while running following query on SQL Server 2000.

This query is basically used to find last location of each unit that are passed. Here I am passing data like “‘26','27','28','29','30','31','32','33','34','35','36','37','38','39','40','41','42','43','44','45','46','47','48','49','50'" in @Units variable. But it takes too much time and I don’t get output. Table is having around 5 Million records.

Query:

SELECT    Alias, tblUnit.UnitID, Location, Latitude, Longitude,Convert(varchar(10),UnitHistoryDate,1) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),12,9) + ' ' + substring(Convert(varchar(40),UnitHistoryDate,109),25,2) as [Date/Time], unithistoryid
FROM tblUnit INNER JOIN
tblUnitHistory ON tblUnit.UnitID = tblUnitHistory.UnitID
WHERE tblUnitHistory.UnitHistoryDate
IN (SELECT MAX(UnitHistoryDate) FROM tblUnitHistory WHERE tblUnitHistory.UnitID in (' + @Units + ') GROUP BY tblUnitHistory.UnitID)
AND tblUnit.UnitID in (' + @Units + ')
ORDER BY tblUnit.UnitID

Table Structure:

UnitHistoryID                             int   Primary Key
UnitID                                       int
Location                                    varchar(200)
Latitude                                     decimal            9
Longitude                                  decimal            9
Speed                                       decimal 5
BatteryVoltage                           decimal 5
ReasonCode                              int
DistanceFromLastLocation            decimal 9
UnitHistoryDate                         datetime

Indexes:

1. Clustered Index on Column UnitID
2. Non-clustered Index on Column UnitHistoryDate
3. Non-clustered Index on Column UnitHistoryID

Please help me to write optimize query and suggest me the steps to require making this query faster. Any help is appreciated. Thanks in advance.

Regards,
Sandeep

View 1 Replies View Related

Optimize This Query?

Oct 12, 2007

I have a nightly job that aggregates data into a summary table. Each night, an additional item may or may not be added, so the table is cleared and repopulated. I've got this working fine. Now, I am being asked to track when an item first appears in the summary table. Here is the code I am using to do this. I realize it is very poor performance, but I don't know how to make it better.
MyTable Columns: id INT PK ,v1 VARCHAR(4) ,v2 VARCHAR(10 ,v3 DECIMAL ,item1 BIT ,item2 BIT ,item3 BIT
MyTable2 Columns: v1 VARCHAR(4) ,v2 VARCHAR(10 ,v3 DECIMAL ,item INT ,FirstKnownDate DATETIME
All columns are NOT NULL.1 DECLARE @iNextRowID INT
2 ,@iCurrentRowID INT
3 ,@iLoopControl INT
4 ,@v1 VARCHAR(4)
5 ,@v2 VARCHAR(10)
6 ,@v3 DECIMAL
7
8 /* This is a loop that executes for a dynamic number of items, eg item1=1, then item2=1, etc */
9 SET @iLoopControl=1
10 SELECT @iNextRowID=MIN(id) FROM MyTable WHERE item1=1
11
12 IF ISNULL(@iNextRowID,0) <> 0 BEGIN
13 SELECT @iCurrentRowID = id
14 ,@v1 = v1
15 ,@v2 = v2
16 ,@v3 = v3
17 FROM MyTable
18 WHERE id=@iNextRowID
19
20 WHILE @iLoopControl = 1 BEGIN
21 IF NOT EXISTS(SELECT * FROM MyTable2 WHERE v1=@v2 AND v2=@v2 AND v3=@v3 AND item=1) BEGIN
22 INSERT INTO MyTable2
23 /* COLUMN */ (v1,v2,v3,item,firstknowndate)
24 SELECT @v1, @v2, @v3, 1, GETDATE()
25 END
26
27 SELECT @iNextRowID = NULL
28 SELECT @iNextRowID = MIN(id) FROM MyTable WHERE id>@iCurrentRowID AND item1=1
29
30 IF ISNULL(@iNextRowID,0) = 0 BEGIN
31 BREAK
32 END
33
34 SELECT @iCurrentRowID = id
35 ,@v1 = v1
36 ,@v2 = v2
37 ,@v3 = v3
38 FROM MyTable
39 WHERE id=@iNextRowID
40 END
41 END42 /* This is the end of the items loop */
This query takes 5 minutes to do straight inserts without the test for exists in the loop for 3 items. MyTable has just under 96,000 rows in it. MyTable2 has 121,857 rows in it. It is not about 55 minutes and it is still running with the exists so that a row isn't inserted twice. Is there anything I can do to optimize this? EDIT: The query just finished running again with the test for exists on each row. It shouldn't have inserted any new rows, but it took 57 minutes and inserted another 114,115 rows. Not good. Anyone see what is wrong with the query?

View 4 Replies View Related

Optimize SQL Statment

Mar 9, 2008

Hello, I have three sql select statments I would like to combine into one. I have created a statment that works but I am not sure if it is a good solution performance wise. Is there a better way to run this query?
Thanks Very Much!!
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate)
Begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where update_freq = 0 and nextupdate < GetDate() Order By nextupdate
End
else
if exists (Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc)
begin
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Where nextupdate < GetDate() Order By importance desc
end
else
Select Top 1 snapsht_id, snpsht_flname, site_url, iWidth, iHeight, isFullPage, fp_flname, fp_iWidth, fp_iHeight
From SiteIndex Order By nextupdate

View 1 Replies View Related

Can't Seem To Find A Way To Optimize This...

Nov 14, 2007

Hi, We have surveys that gather data and we're trying to find the best way to query this data for reporting. It is not too late to change everything so we're open to any suggestions. Here is my current setup:

Results table from which each row represents a user that filled in the survey so it contains a unique identifier, a survey identifier and some other data which we use elsewhere.

Result_Answers table which holds the answers for each result. So it contains the result ID, the question ID and the answer ID

Questions and Answers table which hold the actual text for each identifier used in result_answers.

What we're trying to do is retrieve the count for each answers (thats easy) based on a set of conditional answers. For exemple:

Q1 - Are you male or female

Q2 - What is your age group

Q3 - What type of music do you listen to.

So lets say I want the count of each answers for each question but limited to the results that answered male on Q1 and 18-25 OR 25-35 on Q2. The only way I found to do this is create a cursor to loop trough each questions, select the result IDs that answered Q1 correctly and then remove from that temp table each result ID that didn't answer the subsequent questions correctly. This gives me a page long query with a cursor and multiple IN commands (for when there's more than one answer valid for a single question)

I'm not saying the results are abysmal, I'm still getting my results inside 2s with a bit over 1 million rows in those tables. But unfortunatly, this is basically only one survey... I can only imagine that it will get longer and longer as we add more surveys.

So my question is: Is there a better way to query this data or worst case, is my table schema not efficient enough? In both cases, how can I fix/optimize it?

I'm really hoping you guys can help me out!

View 1 Replies View Related

Need Help To Optimize Query

May 25, 2005

Hi all,

I need help to optimize this query:

Code:

SELECT s.konto, s.sifra_partnera, s.temeljnica, s.rbr, s.duguje, s.potrazuje, s.saldo
FROM saldakonti s
WHERE
s.datum<'2005-01-01' AND
s.uplata='R' AND
EXISTIS (SELECT t.temeljnica, t.rbr FROM saldakonti t WHERE
t.konto=s.konto AND
t.sifra_partnera=s.sifra_partnera AND
t.vezni_broj=s.vezni_broj and
t.datum<'2005-01-01' AND
(t.uplata='S' or t.uplata='U')
GROUP BY t.temeljnica, t.rbr
HAVING
abs(sum(t.duguje-t.potrazuje))<>abs(s.saldo))
ORDER BY
s.konto, s.sifra_partnera, s.temeljnica, s.rbr, s.duguje, s.potrazuje, s.saldo

Currently this query takes forever to execute. Any suggestions appreciated.
Thanks in advance.

View 2 Replies View Related

Best Practices To Optimize A DB ?

Sep 1, 2004

What are the best pactices to optimize performance accsiing an SQLServer DB ?

commands, mantenance plan...

Thanks

View 1 Replies View Related

How To Optimize DB With Command

Feb 17, 2012

is there any command to optimize a database for eg: after 1 month if DB is full of mess then with one command every thing will be in sorted order or something like that to increase the efficiency and speed, is there any thing like that in MSSQL 2005?

View 2 Replies View Related

Optimize Query

Feb 26, 2004

Can someone look at this sql statement and tell me if it can be sped up? Also I have to add to it by joining it with another table. How do I do that? Just by nesting another join?

Thanks!

Set rs=Server.CreateObject("ADODB.Recordset")
sql = "SELECT td.TeamID, td.TeamName, rt.PartID, rt.Effort, rt.UnitMeas, pd.MinMilesConv "
sql = sql & "FROM TeamData td INNER JOIN PartData pd ON td.TeamID = pd.TeamID "
sql = sql & "JOIN RunTrng rt ON pd.PartID = rt.PartID "
sql = sql & "WHERE rt.TrngDate >= '" & Session("beg_date") & "' AND rt.TrngDate < '" & Session("end_date")
sql = sql & "' AND pd.Archive = 'N' AND pd.Gender = '" & sGender & "' AND pd.Grade >= " & iMinGrade
sql = sql & " AND pd.Grade <= " & iMaxGrade & " ORDER BY td.TeamID"
rs.Open sql, conn, 1, 2

View 2 Replies View Related

Query Optimize.

Mar 25, 2004

Hi all,

Table 'A' is having 105 fields & 233000 records.
One Clusterd Primary Key & 10 nonclusterd key.

If I joined with table 'A' or 'Select * from 'A' , Query takes more time so please let me know best way to structure query or table, Indexes etc.

Reply to me asap positivaly ....

Regards,
M. G.

View 1 Replies View Related

Optimize Query.

Apr 1, 2004

Hi All,

please look into below query.

SELECT TOP 101 Cert_WarehouseDetails.CertID, Cert_WarehouseDetails.UnderwriterID,
Cert_WarehouseDetails.WarehouseEntriesID, Cert_WarehouseDetails.DivisionWarehouseLocID,
Cert_WarehouseDetails.TypeID, Cert_WarehouseDetails.ReportingType, Cert_WarehouseDetails.CertWHID,
Cert_WarehouseDetails.ClientPolicyDivisionID, Cert_WarehouseDetails.RecordNO, Cert_WarehouseDetails.InsuredValueX,
Cert_WarehouseDetails.PremiumTotalX, Cert_WarehouseDetails.StatusX, Cert_WarehouseDetails.StatusID,
Cert_WarehouseDetails.AuthorizeDateX, Cert_WarehouseDetails.CodeX, Cert_WarehouseDetails.IssuedDateX, Cert_WarehouseDetails.BillofLadingDateX,
DOGX, Cert_WarehouseDetails.ConveyanceNameX, Cert_WarehouseDetails.LogonNameX , Cert_WarehouseDetails.ClientNameX,
(CASE WHEN UnderwriterID = 0 THEN ' ' ELSE tblClientUsers.FirstName + ' ' + tblClientUsers.LastName END) AS OwnedBy,
(CASE WHEN UnderwriterID = 0 THEN CAST(' ' AS NVARCHAR) ELSE CAST(TakeOwnershipDate as nvarchar) END) AS OwnedDate

FROM
( SELECT tblCertificates.[CertID] AS CertID, tblCertificates.[UnderwriterID] AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, NULL AS WarehouseEntriesID, NULL AS DivisionWarehouseLocID , tblCertificates.CertID AS CertWHID, tblCertificates.ClientPolicyDivisionsID AS ClientPolicyDivisionID, tblCertificates.CertificateNo AS RecordNO, tblCertificates.TotalInsured AS InsuredValueX, (CASE WHEN tblCertificates.Status=101 or tblCertificates.Status=104 or tblCertificates.DivReferral=1 THEN 0 ELSE PremiumTotal-tblCertificates.Discount END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblCertificates.Status < '105' and tblCertificates.Status <> '103' THEN null ELSE AuthorizeDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblCertificates.IssuedDate AS IssuedDateX, tblCertificates.BillofLadingDate AS BillofLadingDateX, tblCertificates.DescriptionofGoods AS DOGX, tblCertificates.ConveyanceName AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblCertificates.TakeOwnershipDate AS TakeOwnershipDate, tblCertificates.ClientID AS ClientID, tblCertificates.Producer AS BrokerID, tblCertificates.SBU AS SBU, tblCertificates.AssociationID AS AssociationID, tblCertificates.AssuredName AS AssuredName, tblCertificates.UserID AS UserID, tblCertificates.Demoflag AS Demoflag FROM tblCertificates, tblReportingType,tblcurrency,tblClientUsers,tblCli ent,tblStatus WHERE tblCertificates.reportType = tblReportingType.TypeID AND tblCertificates.PremiumCurrencyType = tblCurrency.ID AND tblclientUsers.UserID = tblCertificates.UserID AND tblcertificates.ClientId = tblclient.ClientID AND tblStatus.StatusID = tblCertificates.Status
UNION
SELECT NULL AS CertID, NULL AS UnderwriterID, tblReportingType.[TypeID] AS TypeID, tblReportingType.[ReportingType] AS ReportingType, tblWarehouseEntries.[WarehouseEntriesID] AS WarehouseEntriesID, tblWarehouseEntries.[DivisionWarehouseLocID] AS DivisionWarehouseLocID , tblWarehouseEntries.WarehouseEntriesID AS CertWHID, tblWarehouseEntries.ClientPolicyDivisionID AS ClientPolicyDivisionID, tblWarehouseEntries.WarehouseEntriesID AS RecordNO, (CASE WHEN ValueCurrencyType=0 THEN 0 ELSE UnitValue END) AS InsuredValueX, (CASE WHEN tblWarehouseEntries.StatusID=101 THEN 0 ELSE PremiumSum END) AS PremiumTotalX, tblStatus.Description AS StatusX, tblStatus.StatusID AS StatusID, (CASE WHEN tblWarehouseEntries.StatusID < '105' THEN null ELSE ApprovalDate END) AS AuthorizeDateX, tblCurrency.Code AS CodeX, tblWarehouseEntries.IssuedDate AS IssuedDateX, tblWarehouseEntries.PeriodEndDate AS BillofLadingDateX, LocName AS DOGX, '' AS ConveyanceNameX, tblClientUsers.LogonName AS LogonNameX , tblClient.ClientName AS ClientNameX, tblWarehouseEntries.TakeOwnershipDate AS TakeOwnershipDate, tblWarehouseEntries.ClientID AS ClientID, tblWarehouseEntries.BrokerID AS BrokerID, tblWarehouseEntries.SBU AS SBU, tblWarehouseEntries.AssociationID AS AssociationID, tblWarehouseEntries.AssuredName AS AssuredName, tblWarehouseEntries.UserID AS UserID, tblWarehouseEntries.Demoflag AS Demoflag FROM tblWarehouseLoc, tblWarehouseEntries, tblReportingType,tblCurrency ,tblClientUsers,tblClient, tblDivisionWarehouseLoc,tblStatus WHERE tblWarehouseEntries.reportTypeID = tblReportingType.TypeID and tblWarehouseLoc.WarehouseLocationID = tblDivisionWarehouseLoc.WarehouseLocID and tblDivisionWarehouseLoc.DivisionWarehouseLocID = tblWarehouseEntries.DivisionWarehouseLocID and tblWarehouseEntries.PremiumCurrencyType = tblCurrency.ID and tblWarehouseEntries.UserID = tblClientUsers.UserID and tblClient.ClientID = tblWarehouseEntries.ClientID AND tblStatus.StatusID = tblWarehouseEntries.StatusID

) AS Cert_WarehouseDetails LEFT JOIN tblClientUsers ON Cert_WarehouseDetails.UnderwriterID = tblClientUsers.UserID

WHERE 1 = 1 AND Cert_WarehouseDetails.Demoflag = 0 and
(convert(datetime,convert(nvarchar,Cert_WarehouseD etails.IssuedDateX,101)) >= '1/1/2003') and
(Cert_WarehouseDetails.IssuedDateX is not null and
convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004')
ORDER BY Cert_WarehouseDetails.RecordNO

In above query,as
1. Union (INNER) query returns me 200000 records.

2. If I run above query it takes 18 sec because no index on Cert_WarehouseDetails.RecordNO and i'm ordering on that.

3. When I avoid to 'ORDER BY Cert_WarehouseDetails.RecordNO' then query takes 2 sec.

4. In this case Do I have to use 'nvarchar'
convert(datetime,convert(nvarchar,Cert_WarehouseDe tails.IssuedDateX,101)) <= '3/31/2004')

5. Why do we have to use 'nvarchar' can you explain to me in above statement..

Can you provide me alternate solution so I can make it fast.

Please reply to me asap. Thanks in advance.

Regards,
M. Jain

View 10 Replies View Related

How To Optimize This Sql Statement?

May 1, 2004

I have a very simple T-SQL statement which takes hours to run. The statement is something like:

update table1 set column1 = coalesce(table2.column1, ''),
column2 = table2.column2, column3 = table2.column3
from table1, table2
where table1.column4 = table2.column4

The machine is a P3 800 512 ram and 80G HD with 8M cache. I'm running Windows 2000 Server and SQL Server 2000. Table1 has about 500,000 records and Table2 has about 2 millions records. I have added non-clustered indexes for table1.column4 and table2.column4.

When I check Execution Plan, it has Hash Match/Aggregate which costs about 50%.

Thank you for any help.

View 14 Replies View Related