January 23, 2013 at 11:17 pm
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
January 24, 2013 at 1:56 am
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.
January 24, 2013 at 2:06 am
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)
January 24, 2013 at 2:08 am
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.
January 24, 2013 at 2:16 am
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.
January 24, 2013 at 2:27 am
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.
January 24, 2013 at 2:34 am
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;-)
January 24, 2013 at 5:13 am
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.
January 24, 2013 at 5:24 am
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
January 24, 2013 at 5:52 am
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 :]]
January 24, 2013 at 5:55 am
Definition of TB_TRACKING_ABV, TB_WORK_ORDER_AGVEN again with indexes please
January 24, 2013 at 6:03 am
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.
January 24, 2013 at 6:54 am
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
January 25, 2013 at 12:09 am
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.
January 25, 2013 at 2:05 am
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