select and condition column+column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » select and condition column+column

21 posts, Page 1 of 3

1 2 3 »»»

select and condition column+column

Rate Topic

Display Mode

Topic Options

Author

Message

tony28

Posted Wednesday, January 23, 2013 11:17 PM

Valued Member

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 61, Visits: 181

Hello,

is any possible idea for faster performance ?

I have two columns date and sequence, so i need to join this and this is really hard for performance if you have select almost every second and you have lot of data in this table

for example 1column 20130124 - today date and sequence are 0001,0002,0003..

If i want compare with another day, i need to join like string, because i have to get 201301240001 you know, and after i can compare , you know, so its any idea for example for create better index ? or something else how can i select little faster ? I searched on internet, but i dont have idea.

in condition is same, for example

SELECT *
FROM TABLE A (NOLOCK)
WHERE ORDER_DATE+COMMIT_NO
NOT IN
(SELECT ORDER_DATE+COMMIT_NO
FROM TABLE1 B(NOLOCK)
WHERE ORDER_DATE >= A.LAST_ORDER_DATE

thank you for response

Post #1410908

anthony.green

Posted Thursday, January 24, 2013 1:56 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Please follow the link in my signature on posting performance problems and posting code and data for the best help.

First off I would get rid of the NOLOCK hints, unless you can live with the problems it causes. It is not a go faster switch.

Please post table definitions, index definitions, sample data, expected outcome along with the execution plan in a SQLPLAN file.

Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1410987

subbareddy542

Posted Thursday, January 24, 2013 2:06 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 832, Visits: 613

nolock option it good for reading data.

pls create index on ORDER_DATE,COMMIT_NO column
after that pls excute below code.
select * from
(SELECT *,ORDER_DATE+COMMIT_NO new_id
FROM TABLE A (NOLOCK))k
WHERE new_id
NOT IN
(SELECT ORDER_DATE+COMMIT_NO
FROM TABLE1 B(NOLOCK)
WHERE ORDER_DATE >= A.LAST_ORDER_DATE)

Post #1410991

anthony.green

Posted Thursday, January 24, 2013 2:08 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

subbareddy542 (1/24/2013)

nolock option it good for reading data.

What???????? NOLOCK option is not always good for reading data.

Do you understand the problems that NOLOCK give you?

Before you start putting query hints on your code, you could tune your code and analyse why it isnt functioning without the hint.

Post #1410993

subbareddy542

Posted Thursday, January 24, 2013 2:16 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 4:17 AM
Points: 832, Visits: 613

adv of nolock it is removing dead lock.
for live data nolock option don't use.
if you normailly not live data that time nolock will more useful.

Post #1410995

anthony.green

Posted Thursday, January 24, 2013 2:27 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

NOLOCK - Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction

So if you can live with missing data, incorrect data, duplicated data, go ahead and use NOLOCK, if not then dont use it.

Post #1410999

Bhuvnesh

Posted Thursday, January 24, 2013 2:34 AM

SSCrazy

Group: General Forum Members
Last Login: Tuesday, March 26, 2013 8:41 AM
Points: 2,562, Visits: 3,451

subbareddy542 (1/24/2013)

adv of nolock it is removing dead lock.
for live data nolock option don't use.
if you normailly not live data that time nolock will more useful.

this is probably the immatured information .
see this link http://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

-------Bhuvnesh----------
While 1 = 1 (Learning SQL....)
Click to get fast response of your post

Post #1411003

tony28

Posted Thursday, January 24, 2013 5:13 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 61, Visits: 181

thanks for response
i think that here is nolock needed, because this data are live, you know, this select will be every second and i work in Car company, data are still moving.

subbareddy542 - i try read this, but i post more information

Here is the select,
- so Tracking you know, there are data still change, there is just 7rows (position )
- TB_reinput are commit_no(sequences - 0001,0002-1230 etc.), which were canceled, we have table, where we write this
- so and if i want check i need to compare but there was problem because you have to compare both of them ( ORDER_DATE+COMMIT_NO), becuase commit_no are every day same. 0001-xxxx ...

if I start select without compare with TB_REINPUT is fast

SELECT STATION_ID, LAST_COMMIT_NO AS COMMIT_NO
FROM MCS_MESDB.dbo.TB_TRACKING_AGV TR (NOLOCK)
WHERE ORDER_DATE+COMMIT_NO NOT IN
(SELECT ORDER_DATE+ COMMIT_NO FROM MCS_MESDB.dbo.TB_REINPUT (NOLOCK) WHERE ORDER_DATE >= TR.LAST_ORDER_DATE AND LINE_CODE= TR.LINE_CODE)

TABLE reinput has just 759 rows and we add to this sometimes, but condition is just for specify day - last day , i have rebuild index for every saturday if the fragmentation is higher than 20 or 30 i am not sure.
here is index for TB_REINPUT

ALTER TABLE [dbo].[TB_REINPUT] ADD CONSTRAINT [PK_TB_REINPUT] PRIMARY KEY CLUSTERED
(
[ORDER_DATE] ASC,
[COMMIT_NO] ASC,
[LINE_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
GO

So i need to get faster solution if its possible.
I need get just select of 7 rows, first column is station-position and second column is the commit_no, so and if i have some commit_no+order_date inside, i dont want to select this in result . do you understand?

SSC Eights!
I tried your solution and the in execution plan were same

and i have idea, how can i say to sql, that it must scan and not seek?? any hint with specification ??

in attachment is image with execution plan , you can see with and without. and i dont know, if not be better scan than seek in small table like this.

Post Attachments

2222222.jpg (3 views, 105.83 KB)

Post #1411072

anthony.green

Posted Thursday, January 24, 2013 5:24 AM

SSCertifiable

Group: General Forum Members
Last Login: Friday, April 12, 2013 3:51 AM
Points: 5,075, Visits: 4,831

Please post the CREATE TABLE scripts along with all indexes for both tables along with the full query.

You may have an outdated plan, bad statistics, bad index fragmentation which is causing SQL to say a scan is quicker than a seek also it has to do with index selectivity etc

Post #1411077

tony28

Posted Thursday, January 24, 2013 5:52 AM

Valued Member

Group: General Forum Members
Last Login: Today @ 9:05 AM
Points: 61, Visits: 181

anthony.green Here is TB_REINPUT, so i think that not will be problem in second table . If you check the image with sqlplan, there is 77% with seek in TB_REINPUT.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[TB_REINPUT](
[ORDER_DATE] [char](8) NOT NULL,
[COMMIT_NO] [char](4) NOT NULL,
[LINE_CODE] [char](5) NOT NULL,
[CREATE_BY] [nvarchar](10) NULL,
[CREATE_DTTM] [datetime] NULL,
CONSTRAINT [PK_TB_REINPUT] PRIMARY KEY CLUSTERED
(
[ORDER_DATE] ASC,
[COMMIT_NO] ASC,
[LINE_CODE] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

here is almost same query, but with join another table for check not LAST_COMMIT but just COMMIT and this is faster than query before in sql plan. like estimated query and % are 53 vs 43 for this second query,, but in c# program the second little froze program, and first no/

SELECT STATION_ID,
(
SELECT TOP 1 COMMIT_NO
FROM MCS_MESDB.dbo.TB_WORK_ORDER_AGVEN WO
WHERE TR.LAST_ORDER_DATE + TR.LAST_COMMIT_NO < WO.ORDER_DATE + WO.COMMIT_NO
AND DATA_TYPE <> 'SD'
AND WO.ORDER_DATE + WO.COMMIT_NO NOT IN (SELECT ORDER_DATE+COMMIT_NO FROM MCS_MESDB.dbo.TB_REINPUT WHERE ORDER_DATE >= TR.LAST_ORDER_DATE AND LINE_CODE= TR.LINE_CODE)
ORDER BY ORDER_DATE ASC, COMMIT_NO ASC

) COMMIT_NO
FROM MCS_MESDB.dbo.TB_TRACKING_AGV TR (NOLOCK)

and statistic are from today and fragmentation is 14% in TB_REINPUT its ok ,

so it was idea, if not will be better scan all table than seek > do you know this hint ? how can i write or force the query use the index with hint scan ?

I hope that isnt confuse :]]

Post #1411092

« Prev Topic | Next Topic »

21 posts, Page 1 of 3

1 2 3 »»»

Permissions