select and condition column+column

  • 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

  • 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.

  • 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)

  • 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.

  • 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.

  • 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.

  • 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;-)

  • 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.

  • 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

  • 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 :]]

  • Definition of TB_TRACKING_ABV, TB_WORK_ORDER_AGVEN again with indexes please

  • Post the query which is giving you performance problems dont keep changing the query.

    Then from that query, post the definition of all tables used within that query along with all of their indexes.

  • OK sorry, tommorow i will send direct this query, this is what i wrote second, because i founded after that we need the second.

    Now i am not at work, tommorow at 7:00 i will send..

    thank you for response

  • hello, so programmer solved this frozen with thread, but he said me it will be temporary solution if I tune the query, if its possile

    so in attachment i add files like on your web, and inside the files are little notices for this

    and I forgot. this query is in procedure.

  • No additional indexes on the tables?

    Just primary keys?

    Also provide your data in a consumable format, like you have with your tables

    E.g.

    INSERT INTO TB_WORK_ORDER_ABV VALUES ('dfewrgre','ggergerg','gregergher','gerhreher')

Viewing 15 posts - 1 through 15 (of 20 total)

You must be logged in to reply to this topic. Login to reply