How To Improve Performance Of 'LEFT JOIN'

May 18, 2006

I am developing reporting service and using lots of 'LEFT OUTER JOIN',
I am worried about the performance and want to use some subquery to
improve
the performance.
Could I do that like below,

[the origin source]
SELECT *
FROM TableA
LEFT OUTER JOIN TableB
ON TableA.item1 = TableB.item1
WHERE TableA.item2 = 'xxxx'
TableB.item2 > yyyy AND TableB.item2 < zzzz

I add the subquery to query every table before 'LEFT JOIN'
--------------------------------------------------------------------------
SELECT *
FROM
(SELECT *
FROM TableA
WHERE TableA.item2 = 'xxxx'
) TableC
LEFT OUTER JOIN
(SELECT *
FROM TableB
WHERE TableB.item2 > yyyy AND TableB.item2 < zzzz
) TableD
ON TableC.item1 = TableD.item1
WHERE TableC.item2 = 'xxxx'
TableD.item2 > yyyy AND TableD.item2 < zzzz
--------------------------------------------------------------------------

Can anyone give me some suggestion?
Thanks a lot.

Leland Huang

View 2 Replies


ADVERTISEMENT

How To Improve Performance If Inner Join Has More Than 2 Or 3 Tables

Aug 15, 2006

Hi everyone
     I need a solution for this query. It is working fine for 2 tables but when there are 1000's of records in each table and query has more than 2 tables. The process never ends.
Here is the query
(select siqPid= 1007, t1.Gmt909Time as GmtTime,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as EngValue,
 t1.Loc1Time as locTime,t1.msgId
 into #temp5
 from #temp1 as t1,#temp2 as t2,#temp3 as t3,#temp4 as t4
 where t1.Loc1Time = t2.Loc1Time and t2.Loc1Time = t3.Loc1Time and t3.Loc1Time = t4.Loc1Time)
 I was trying to do something with this query.
 
But the engValues cant be summed up. and if I add that in the query, the query isnt compiling.
(select siqPid= 1007, t1.Gmt909Time as GmtTime,
 t1.Loc1Time as locTime,t1.msgId,(t1.engValue+t2.engValue+t3.engValue+t4.engValue) as engValue
 --into #temp5
 from #temp1 as t1
 where exists
(Select 1
  from #temp2 as t2
 where t1.Loc1Time = t2.Loc1Time and
  exists
(Select 1
  from #temp3 as t3
 where t2.Loc1Time = t3.Loc1Time and
   exists
(Select 1
 from #temp4 as t4
 where t3.Loc1Time = t4.Loc1Time))))
 
 
I need immediate help on that, I would appreciate an input on it.
 
Thanks
-Sarah

View 15 Replies View Related

How To Improve Performance With A Join Between 2 Table From 2 SQL Servers

Aug 18, 2006

I am making a ASP.NET web application that involves 2 SQL Server(A & B).
I created a view in SQL server A pointing to the table in SQL Server B. I found out my application will run REALLY slow when accessing such a view. so I try to avoid using them. But in the case of 2 table joining from 2 different SQL Servers, I have no choice.
Can anyone help me with this?
Thanks!

View 4 Replies View Related

Left Outer JOIN Proceducing Very Slow Performance...

Oct 28, 2007

Hi,

I'm executing a nested queries consisting of LEFT OUTER JOIN for:
Duration = 27 sec;
Reads = 1690;
Number of users = 1.

This is extremely slow for a small set of data. I'm afraid that when we have a larger dataset and more concurrent users that query will take forward.

What am I doing wrong for my query to be taking too long?

Here's the query:


SELECT B.Business_Id as Business1_0_,

B.Place_Of_Business_Id as Place2_30_0_,

B.Business_Type_Id as Business3_30_0_,

B.Business_Name as Business4_30_0_,

B.Business_Description as Business5_30_0_,

B.Last_Update_Timestamp as Last6_30_0_

FROM Busines as B

LEFT OUTER JOIN Business_Service as BS

ON B.Business_Id = BS.Business_Id

LEFT OUTER JOIN Business_Service_Category as BSC

ON B.Business_Id = BSC.Business_Id

LEFT OUTER JOIN Business_Sub_Category as BSSC

ON B.Business_Id = BSSC.Business_Id

where B.Business_Id IN (

Select B.Business_Id from Busines as B

JOIN Business_Address as BA ON B.Business_Id = BA.Business_Id

JOIN Address as A ON A.Address_Id = BA.Address_Id

WHERE A.City_Name like '%New York%'

and A.state = 'NY') AND ( FREETEXT(B.Business_Name, 'Designer')

or BSC.Service_Category_Id IN

( Select SC.Service_Category_Id from Service_Category as SC

where FREETEXT(SC.Service_Category_Name, 'Designer') )

or BSSC.Sub_Category_Id IN (

Select SSC.Service_Sub_Category_Id from Service_Sub_Category as SSC

where FREETEXT(SSC.Service_Sub_Category_Name, 'Designer') )

or BS.Service_Id IN (

Select S.Service_Id from Service as S

where FREETEXT(S.Service_Name, 'Designer') ) )

----------------------------------------------------------------------------------------------------------------------------------------
Here's the SQL Query Plan:

|--Nested Loops(Left Semi Join, OUTER REFERENCES.[Business_Id], [BS].[Service_Id], [BSC].[Service_Category_Id], [BSSC].[Sub_Category_Id]))
|--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Sub_Category].[Business_Id] as [BSSC].[Business_Id]))
| |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service_Category].[Business_Id] as [BSC].[Business_Id]))
| | |--Nested Loops(Left Outer Join, WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]=[PB].[dbo].[Business_Service].[Business_Id] as [BS].[Business_Id]))
| | | |--Nested Loops(Inner Join, OUTER REFERENCES.[Business_Id]))
| | | | |--Sort(DISTINCT ORDER BY.[Business_Id] ASC))
| | | | | |--Nested Loops(Inner Join, OUTER REFERENCES[BA].[Business_Id]))
| | | | | |--Hash Match(Inner Join, HASH.[Address_Id])=([BA].[Address_Id]), RESIDUAL[PB].[dbo].[Address].[Address_Id] as .[Address_Id]=[PB].[dbo].[Business_Address].[Address_Id] as [BA].[Address_Id]))
| | | | | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Address].[PK__Address__0519C6AF] AS ), WHERE[PB].[dbo].[Address].[State] as .[State]='NY' AND [PB].[dbo].[Address].[City_Name] as .[City_Name] like '%JayVille%'))
| | | | | | |--Index Scan(OBJECT[PB].[dbo].[Business_Address].[ClusteredIDX_Business_Address] AS [BA]))
| | | | | |--Index Seek(OBJECT[PB].[dbo].[Busines].[UI_ukBusiness] AS ), SEEK.[Business_Id]=[PB].[dbo].[Business_Address].[Business_Id] as [BA].[Business_Id]) ORDERED FORWARD)
| | | | |--Clustered Index Seek(OBJECT[PB].[dbo].[Busines].[PK_Busines] AS ), SEEK.[Business_Id]=[PB].[dbo].[Busines].[Business_Id] as .[Business_Id]) ORDERED FORWARD)
| | | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service].[ClusterIDX_Business_Service] AS [BS]))
| | |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Service_Category].[ClusterIDX_Business_Service_Category] AS [BSC]))
| |--Clustered Index Scan(OBJECT[PB].[dbo].[Business_Sub_Category].[ClusterIDX_Business_Sub_Category] AS [BSSC]))
|--Concatenation
|--Filter(WHERE[PB].[dbo].[Busines].[Business_Id] as .[Business_Id] = [Full-text Search Engine].[KEY]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Category].[PK__Service_Category__15502E78] AS [SC]), SEEK[SC].[Service_Category_Id]=[PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service_Category].[Service_Category_Id] as [BSC].[Service_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
| |--Clustered Index Seek(OBJECT[PB].[dbo].[Service_Sub_Category].[PK_Service_Sub_Category] AS [SSC]), SEEK[SSC].[Service_Sub_Category_Id]=[PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]) ORDERED FORWARD)
| |--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Sub_Category].[Sub_Category_Id] as [BSSC].[Sub_Category_Id]))
| |--Remote Scan(OBJECTFREETEXT))
|--Nested Loops(Left Semi Join)
|--Clustered Index Seek(OBJECT[PB].[dbo].[Service].[PK__Service__117F9D94] AS ), SEEK.[Service_Id]=[PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]) ORDERED FORWARD)
|--Filter(WHERE[Full-text Search Engine].[KEY] = [PB].[dbo].[Business_Service].[Service_Id] as [BS].[Service_Id]))
|--Remote Scan(OBJECTFREETEXT))


Please Help....



View 2 Replies View Related

Performance Related Doubt On The Following Items --&&> LEFT/INNER JOIN Vs IN And Cost Of CONVERT

May 28, 2008

Hi,

1.
Right now in my queries I am using lots of LEFT Joins and INNER JOINs... and I was suggested to look at 'IN'... But with IN I did face some performance issues previously and stopped using it... but I have got new doubts on which query will give me better performance...

A query using LEFTJoin or a query using IN/NOT-IN


2.
This question is about CONVERT...

I have a stored proc which is used for updating a table... and multiple columns [of the same table] and corresponding values are sent to the proc [only a subset of the columns might be sent for updates everytime and the columns to update is not fixed for each run of the SP]...

I have to construct a UPDATE String out of it using string concatenation to finally be able to use "sys.sp_executesql" on that update statement...

This results in me having to use CONVERT() lots of times... and one of the columns among them on which I am doing a CONVERT is of the type XML...

So the question is as follows...
a. Is it preferrable to construct a single UPDATE statement string and execute it using "sys.sp_executesql"
b. Or Is it preferrable to give multiple UPDATE statments... i.e. one update statement for each column [Depending on whether that column has to be updated for that run or not]


i.e. The question essentially is:

Does a single update query constructed using lots of CONVERTS [Basically on INT and XML types]
give more performance over using multiple UPDATE statments on the table
Or is it the other way round..

Thanks,
Pratap.

View 5 Replies View Related

Left Join Vs Left Outer Join Syntax Generates Different Execution Plans

Apr 16, 2008



Anyone know why using

SELECT *
FROM a LEFT OUTER JOIN b
ON a.id = b.id
instead of

SELECT *
FROM a LEFT JOIN b
ON a.id = b.id

generates a different execution plan?

My query is more complex, but when I change "LEFT OUTER JOIN" to "LEFT JOIN" I get a different execution plan, which is absolutely baffling me! Especially considering everything I know and was able to research essentially said the "OUTER" is implied in "LEFT JOIN".

Any enlightenment is very appreciated.

Thanks

View 5 Replies View Related

Left Join Vs Left Outer Join

Apr 7, 2008

Is there any difference between left join and left outer join in sql server 2000?please reply with example if any?
Thanks in advance

View 13 Replies View Related

Left Join Vs Left Outer Join

May 14, 2008

Hi,

Whats the diference between a left join and a left outer Join

View 5 Replies View Related

Using SQL Profiler To Improve Performance

Mar 8, 2004

Hi,
We have a poorly performing SQL 2000 db. i have just defragged ( the HD, not indexes, these are done daily via SQL Agent) the data files of our server and have not found any improvement in response.
I have now got into using SQL profiler to analyse the server performance. in the results that the trace is returning there are some huge (REALLY BIG) values for the duration and cpu values but these rows have no textdata value returned (ie it is null)

why is this? for these rows, the reads and writes columns are also high.

if these rows are what is taking the cpu's time then how can i identify what the server is doing to make any changes?

any thoughts on what other values i might trace or what action i can take to find the slow down cause?

in performance manager the processors (dual Xeons) are rarely dropping below 60%.


thanks in advance

fatherjack

View 2 Replies View Related

Help Me To Improve My Application Performance

Mar 13, 2007

Dear Experts,
I'm a DBA, Working for a Product based company.
We are implementing our product for a certain client of huge OLTP.
our reports team is facing problem (error: all the reports are timed out).though the queries are written properly, Each query is taking some minutes of time.
I've given the command DBCC DROPCLEANBUFFERS.
the time immediately dropped to 10 sec.

now my question is :
please suggest me the DBCC commands or any DBA related commands to improve the performance of the application for my reports team.

Thanks in advance.

Vinod

View 4 Replies View Related

Performance And Database Improve Help

Jan 15, 2008

Hi All,

from your experience in SQL 2005 - do i have any free software that can help in improve performance or can help in identifying performance bottleneck. two examples of performance and help that i use usually use are the maintenance plan that do (check DB > reorganized index > rebuild index > update statics) and the second software is the SQL 2005 DASHBOARD for the reporting help.
do you have any other free tools and help that you can give me for performance or any thing that i must have in my SQL 2005 servers.

Thx

View 3 Replies View Related

Improve Performance In Query..

Jul 20, 2005

I have a table called work_order which has over 1 million records and acontractor table which has over 3000 records.When i run this query ,it takes long time since its grouping bycontractor and doing multiple sub SELECTs.is there any way to improve performance of this query ??-------------------------------------------------SELECT ckey,cnam,t1.contractor_id,count(*) as tcnt,(SELECT count(*) FROM work_order t2 WHEREt1.contractor_id=t2.contractor_id and rrstm=1 and rcdt is NULL) as r1,(SELECT count(*) FROM work_order t3 WHEREt1.contractor_id=t3.contractor_id and rrstm=2 and rcdt is NULL) as r2,(SELECT count(*) FROM work_order t4 WHEREt1.contractor_id=t4.contractor_id and rrstm=3 and rcdt is NULL) as r3,SELECT count(*) FROM work_order t5 WHEREt1.contractor_id=t5.contractor_id and rrstm=4 and rcdt is NULL) as r4,(SELECT count(*) FROM work_order t6 WHEREt1.contractor_id=t6.contractor_id and rrstm=5 and rcdt is NULL) as r5,(SELECT count(*) FROM work_order t7 WHEREt1.contractor_id=t7.contractor_id and rrstm=6 and rcdt is NULL) as r6,SELECT count(*) FROM work_order t8 WHEREt1.contractor_id=t8.contractor_id and rcdt is NULL) as open_count,(SELECT count(*) FROM work_order t9 WHEREt1.contractor_id=t9.contractor_id and vendor_rec is not NULL) asAck_count,(SELECT count(*) FROM work_order t10 WHEREt1.contractor_id=t10.contractor_id and (rtyp is NULL or rtyp<>'R') andrcdt is NULL) as open_norwoFROM work_order t1,contractor WHEREt1.contractor_id=contractor.contractor_id andcontractor.tms_user_id is not NULL GROUP BYckey,cnam,t1.contractor_id ORDER BY cnam*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View 2 Replies View Related

How To Improve Performance In This Query?

Jul 20, 2005

Hey guys,Here's my situation:I have a table called lets say 'Tree', as illustred bellow:Tree====TreeId (integer)(identity) not nullL1(integer)L2(integer)L3(integer)....L10(integer)The combination of the values of L1 thru L10 is called a "Path" , andL1 thru L10 values are stored in a second table lets say called'Leaf':Leaf====LeafId (integer)(identity) not nullLeatText varchar(2000)Here's my problem:I need to lookup for a given keyword in each path of the tree table,and return each individual column for the paths that match thecriteria. Here's the main idea of how I have this now.SELECT TreeId,L1,L2,...,L10, GetText(L1) + GetText(L2) as L2text + ...+ GetText(L10) AS PathTextINTO #tmp FROM Tree //GetText is a lookup function for the Leaf tableSELECT L1,GetText(L1),L2,GetText(L2),...,L10,GetText(L10) FROM #tmpWHERECharIndex(@keyword,a.pathtext) > 0Does anyone would know a better,smart, more efficient way toaccomplish this task? :)Thks,

View 1 Replies View Related

Improve The Performance Of This Query...

Dec 14, 2007

SQL Experts,

I'm facing a performance issue with the following query...
The Output of the following Query is 184 Records and it takes 2 to 3 secs to execute the query.


SELECT DISTINCT Column1 FROM Table1 (NOLOCK) WHERE Column1 NOT IN

(SELECT T1.Column1 FROM Table1 T1(NOLOCK) JOIN Table2 T2 (NOLOCK)

ON T2.Column2 = T1.Column2 WHERE T2.Column3= <Value>)


Data Info.

No of records in Table1 --> 1377366

No. of distinct records of Column1 in Table1 --> 33240


Is there any way the above query can be rewritten to improve the performance, which should take less than 1 sec...
(I'm using DISTINCT because there are Duplicate records of Column1 inTable1 )

Any of your help in this regard will be greately appreciated.

--
ash



View 7 Replies View Related

Improve VFP Source Performance

Nov 5, 2007


I am not an expert in either SSIS or VFP technology but know enough to get my way round. One anomaly I did discover I thought was worth sharing for all those concerned with getting large amounts of data out of VFP in as short a time as possible. When you search for performance tips in relation to SSIS the advice is to never use select table or view from data access mode list in ole db source as this effectively translates to select * from table and I've never come across anything to contradict this €“ well I am and let me explain why:

When you use SQL command as data access mode in ole db source (where ole db source is foxpro dbc) and you write out select column1, column 2 etc etc from table a etc etc and then connect that to a destination (in my case ole db destination) the SSIS task spends ages stuck on Pre-execute before anything happens (the bigger the fox tbl the longer the wait). What is happening behind scenes is that the foxpro engine (assuming its foxpro engine and not sql engine €“ either way don€™t think it matters too much) is executing the sql command and then writing results to a tmp (temp) file on your local temp folder €“ (in my case : C:Documents and SettingsautologinLocal SettingsTemp1). These files take up gigs of space and it is only when this process is complete does the SSIS task actually finish the Pre-execute and start the data transfer process. I couldn€™t understand a) why my packages were stuck on pre-execute for such long times? and b) why were the tmp files being created and why they were soo big?

If you change from SQL command in source to Table or view and then select your table from list the SSIS task when executed kicks off immediately and doesn€™t get stuck on pre-execute nor create any tmp files €“ so you save time and disk space. The difference in time is immense and if like me you were really frustrated with poor performance when extracting from VFP now you know why.

Btw maybe this does not apply to all versions of VFP but it certainly does to v7.

View 6 Replies View Related

Query Help To Improve Performance

Jan 2, 2008



Hi,
I have database D1 which contains 5 million users and one more database D2 having 95k Users.
i wanted to insert common users into new database D3 based on filter which is Phone number and is unique value. Below is the structure of my tables in D1 and D2:

D1(database)
UserProfiles(Table)
UserId (Column Name)
UserProfiledata (Column Name)


D2 (database)
Alerts (Table)
PhoneNumbers (ColumnName - Unique)


Now userProfiles table contains data in string format as below:
User.state AA User.City CC User.Pin 1234 User.phonenumber 987654

so iam parsing for each user using cursor and writing phone numbers into some temp table and wanted to query D2 database to verify whether this phone number exists in Alerts Table of D2 database.

can anyone please suggest on how i can go ahead with this and also help me on how to improve perfomance.

Thanks,
-Veera

View 3 Replies View Related

Integration Services :: How To Perform Left Restricted Join In Merge Join Transformation

May 22, 2015

I have two xml source and i need only left restricted data.

how can i perform left restricted join?

View 2 Replies View Related

Transact SQL :: Difference Between Inner Join And Left Outer Join In Multi-table Joins?

Oct 8, 2015

I was writing a query using both left outer join and inner join.  And the query was ....

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
        (Production.Products AS P
         INNER JOIN Production.Categories AS C

[code]....

However ,the result that i got was correct.But when i did  the same query using the left outer join in both the cases

i.e..

SELECT
        S.companyname AS supplier, S.country,P.productid, P.productname, P.unitprice,C.categoryname
FROM
        Production.Suppliers AS S LEFT OUTER JOIN
(Production.Products AS P
LEFT OUTER JOIN Production.Categories AS C
ON C.categoryid = P.categoryid)
ON
S.supplierid = P.supplierid
WHERE
S.country = N'Japan';

The result i got was same,i.e

supplier     country    productid    productname     unitprice    categorynameSupplier QOVFD     Japan     9     Product AOZBW    97.00     Meat/PoultrySupplier QOVFD    Japan   10     Product YHXGE     31.00     SeafoodSupplier QOVFD     Japan   74     Product BKAZJ    10.00     ProduceSupplier QWUSF     Japan    13     Product POXFU     6.00     SeafoodSupplier QWUSF     Japan     14     Product PWCJB     23.25     ProduceSupplier QWUSF    Japan     15    Product KSZOI     15.50    CondimentsSupplier XYZ     Japan     NULL     NULL     NULL     NULLSupplier XYZ     Japan     NULL     NULL     NULL     NULL

and this time also i got the same result.My question is that is there any specific reason to use inner join when join the third table and not the left outer join.

View 5 Replies View Related

To Improve The Performance Of Update Operation...

Oct 20, 2002

I should add an Identity field (Identity=True) and a row version field(timestamp) to my table, and avoid to arrange tables into different databases, is it true in general?

View 4 Replies View Related

Improve Performance On A Query With UNION

Jun 9, 2004

I have a view which uses UNION of two tables. First table has a 1.5 Million records and the second one has 40,000 records. When I query the view with a column (that is indexed in both tables) in the where clause, it's taking taking 3 Minutes to give the result. The column is of DateTime data Type. Any ideas as to how to improve the query performance ???

TIA

-XLDB

View 14 Replies View Related

Help Needed To Improve The Performance Of The Query

Feb 28, 2005

Hello,
I have the following setup and I would appreciate any help in improving
the performance of the query.


BigTable:
Column1 (indexed)
Column2 (indexed)
Column3 (no index)
Column4 (no index)


select
[time] =
CASE
when BT.Column3 = 'value1' then DateAdd(...)
when BT.Column3 in ('value2', 'value3') then DateAdd(...)
END,
Duration =
CASE
when BT.Column3 = 'value1' then DateDiff(...)
when BT.Column3 in ('value2', 'value3') then DateDiff(ss,
BT.OrigTime, (select TOP 1 X.OrigTime from BigTable X where X.Column1 >
BT.Column1 and X.Column3 <> 'value4' order by X.Column1 ))
END,


FROM
BigTable BT where BT.Column3 = 'value1' OR (BT.Column3 in ('value2',
'value3') and BT.Column4 <> (select X.Column4 from BigTable X where
X.Column1 = BT.Column1 and X.Column3 = 'Value1'))


Apart from the above mentioned, there are a few more columns which are
just a part of select statement and are not in any condition statments.


The BigTable has around 1 Mil records and the response time is very
poor, it takes around 3 mins to retrieve the records (which would be
around 500K)


With the Statistics ON,
I get the following:


Table 'BigTable'. Scan count 2, logical reads 44184, physical reads 0,
read-ahead reads 0.
Table 'WorkTable'. Scan count 541221, logical reads 4873218, physical
reads 0, read-ahead reads 0.


Is there any way to increase the performance, so that I can get the
result under 1 minute?
Any help would be appreciated.


P.S: I tried indexing the Column3, but no improvement.

-SR

View 8 Replies View Related

Foreign Keys Improve Performance?

Dec 4, 2006

I have this queston that I cannot get a clear answer on. I have searched the internet to find out if using foreign keys have any performane benefits but some articles yes and some say no. So what should I believe here. Does foreign keys have any performance benefits.

View 4 Replies View Related

Ntext + Nvarchar - Will This Improve Performance?

Jan 28, 2004

Hi all

I have a table that contains an ntext column for storing values up to a couple of Mb in size.

However, I estimate that 95% of the values stored in this ntext field will fit into an nvarchar(4000) field.

Is it worth me having both fields in the table?

i.e. For rows where the values < 4000 characters I would store the value in the nvarchar column. Otherwise I would use the ntext column.

Can anyone confirm whether this technique would increase performance given that ntext values are sort of stored separately to the rest of the table data?

A colleague of mine is an Oracle DBA and he mentioned this technique is fairly caommonly adopted in the Oracle world.

Thanks
Matt

View 1 Replies View Related

Way To Improve Performance Without Using Replace Function

Feb 13, 2014

i have column in table which contains tabs and " i want replace with space...i am using repalce function is thier other way to improve performance with out using replace function.

View 9 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

Help Needed To Improve The Performance Of The Query

Jul 23, 2005

Hello,I have the following setup and I would appreciate any help in improvingthe performance of the query.BigTable:Column1 (indexed)Column2 (indexed)Column3 (no index)Column4 (no index)select[time] =CASEwhen BT.Column3 = 'value1' then DateAdd(...)when BT.Column3 in ('value2', 'value3') then DateAdd(...)END,Duration =CASEwhen BT.Column3 = 'value1' then DateDiff(...)when BT.Column3 in ('value2', 'value3') then DateDiff(ss,BT.OrigTime, (select TOP 1 X.OrigTime from BigTable X where X.Column1 >BT.Column1 and X.Column3 <> 'value4' order by X.Column1 ))END,FROMBigTable BT where BT.Column3 = 'value1' OR (BT.Column3 in ('value2','value3') and BT.Column4 <> (select X.Column4 from BigTable X whereX.Column1 = BT.Column1 and X.Column3 = 'Value1'))Apart from the above mentioned, there are a few more columns which arejust a part of select statement and are not in any condition statments.The BigTable has around 1 Mil records and the response time is verypoor, it takes around 3 mins to retrieve the records (which would bearound 500K)With the Statistics ON,I get the following:Table 'BigTable'. Scan count 2, logical reads 44184, physical reads 0,read-ahead reads 0.Table 'WorkTable'. Scan count 541221, logical reads 4873218, physicalreads 0, read-ahead reads 0.Is there any way to increase the performance, so that I can get theresult under 1 minute?Any help would be appreciated.P.S: I tried indexing the Column3, but no improvement.

View 1 Replies View Related

Is There Any Way To Improve The Performance Of Select Statements In MS SQL?

Jul 20, 2005

Hi All,I am getting slower performance of select statements in MS SQL. I amfinding select statements in MS SQL are even slower than MS ACCESS. Isthere any way to improve the performance of select statements in MSSQL by tuning the database of anything else??Thanks in advance!Hoque

View 3 Replies View Related

UPDATE STATISTICS Necessary To Improve Performance (?)

Jul 20, 2005

Dear Sql Server experts:First off, I am no sql server expert :)A few months ago I put a database into a production environment.Recently, It was brought to my attention that a particular query thatexecuted quite quickly in our dev environment was painfully slow inproduction. I analyzed the the plan on the production server (itlooked good), and then tried quite a few tips that I'd gleaned fromreading newsgroups. Nothing worked. Then on a whim I performed anUPDATE STATISTICS on a few of the tables that were being queried. Thequery immediately went from executing in 61 seconds to under 1 second.I checked to make sure that statistics were being "auto updated" andthey were.Why did I need to run UPDATE STATISTICS? Will I need to again?A little more background info:The database started empty, and has grown quite rapidly in the lastfew months. One particular table grows at a rate of about 300,000records per month. I get fast query times due to a few well placedindexes.A quick question:If I add an index, do statistics get automatically updated for thisnew index immediately?Thanks in advance for any help,Felix

View 17 Replies View Related

Howto Improve Performance Of Tempdb?

Aug 27, 2007

Hi,

We are thinking about buying new harddrives to improve sql server performance. Currently TEMPDB is running on a dedicated RAID 0 with 3 harddrives of 136 GB, 10.000 RPM. When running a large bulk insert within a SSIS package to 15 destination tables we notice high numbers in the Avg. and current Read Queue length (above 3000) of the drive where TEMPB is on. No other programs or swap file is using this RAID 0 drive. Can anyone tell me if it is worth buying 4 harddrives of 15.000 RPM each 33 GB big replacing the current 3 drives? How much impact will it have on the Avg. and Current Read queue length and will it improve the time sql server needs to bulk insert data?

Thanks.

Marc

View 6 Replies View Related

How To Improve Database Searhing Performance?

Mar 22, 2007



Hi, I am using compact framework 1.1 and SQL CE database for my mobile application. My database has a total of 160000 rows of records and whenever i do a query searching, it will take about 20 seconds to look through the whole database if the record does not exist. Is there any method to improve the searching performance? i am using data reader for the query.



Thanks.

View 1 Replies View Related

'Left Outer Merge Join' Failing To Join Valid Row

Aug 10, 2007

Scenario:

OLEDB source 1
SELECT ...
,[MANUAL DCD ID] <-- this column set to sort order = 1
...
FROM [dbo].[XLSDCI] ORDER BY [MANUAL DCD ID] ASC


OLEDB source 2
SELECT ...
,[Bo Tkt Num] <-- this column set to sort order = 1
...
FROM ....[dbo].[FFFenics] ORDER BY [Bo Tkt Num] ASC

These two tasks are followed immediately by a MERGE JOIN

All columns in source1 are ticked, all column in source2 are ticked, join key is shown above.
join type is left outer join (source 1 -> source 2)

result of source1 (..dcd column)
...
4-400-8000119
4-400-8000120
4-400-8000121
4-400-8000122 <--row not joining
4-400-8000123
4-400-8000124
...


result of source2 (..tkt num column)
...
4-400-1000118
4-400-1000119
4-400-1000120
4-400-1000121
4-400-1000122 <--row not joining
4-400-1000123
4-400-1000124
4-400-1000125
...

All other rows are joining as expected.
Why is it failing for this one row?

View 1 Replies View Related

How Do You Improve SQL Performance Over Large Amount Of Data?

Jul 23, 2005

Hi,I am using SQL 2000 and has a table that contains more than 2 millionrows of data (and growing). Right now, I have encountered 2 problems:1) Sometimes, when I try to query against this table, I would get sqlcommand time out. Hence, I did more testing with Query Analyser and tofind out that the same queries would not always take about the sametime to be executed. Could anyone please tell me what would affect thespeed of the query and what is the most important thing among all thefactors? (I could think of the opened connections, server'sCPU/Memory...)2) I am not sure if 2 million rows is considered a lot or not, however,it start to take 5~10 seconds for me to finish some simple queries. Iam wondering what is the best practices to handle this amount of datawhile having a decent performance?Thank you,Charlie Chang[Charlies224@hotmail.com]

View 5 Replies View Related

How Do U Improve Performance Of Database In MS SQL 2000 Or MS SQL 2005?

Mar 11, 2008

What are the ways to do that ?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved