SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select and condition column+column


select and condition column+column

Author
Message
tony28
tony28
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 889
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



How to post data/code on a forum to get the best help: Option 1 / Option 2
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10408 Visits: 6378
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
When a question, really isn't a question - Jeff Smith
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


subbu1
subbu1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 Visits: 695
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)
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10408 Visits: 6378
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
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


subbu1
subbu1
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1051 Visits: 695
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.
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10408 Visits: 6378
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.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
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


Bhuvnesh
Bhuvnesh
SSCertifiable
SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5498 Visits: 4076
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----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
tony28
tony28
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 889
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.



How to post data/code on a forum to get the best help: Option 1 / Option 2
Attachments
2222222.jpg (6 views, 105.00 KB)
anthony.green
anthony.green
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10408 Visits: 6378
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



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
When a question, really isn't a question - Jeff Smith
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


tony28
tony28
SSChasing Mays
SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)SSChasing Mays (638 reputation)

Group: General Forum Members
Points: 638 Visits: 889
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 :]]



How to post data/code on a forum to get the best help: Option 1 / Option 2
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search