table index problem?

[baran121]

hi i have two table which i join them i couldnt get data even when i wanna get even one row .

here is my code :
select top 1 *from kalite_kontrol k inner join is_acma_bar b on k.is_num=b.is_num and k.oper_num=b.oper_num where b.pers_num=272 and k.tarih='2016-03-23'

i couldnt get data by query . i think i need to make index but i tried all possibility thant i know.
for any support i ll be appreciate, please help me.

and my tables details:

CREATE TABLE [dbo].[is_acma_bar] (
[is_num] [bigint] NOT NULL ,
[barkod] [varchar] (10) COLLATE Turkish_CI_AS NOT NULL ,
[pers_num] [bigint] NULL ,
[oper_num] [int] NOT NULL ,
[oper_var] [smallint] NOT NULL ,
[tarih] [smalldatetime] NULL ,
[saat] [datetime] NULL ,
[adet] [int] NOT NULL ,
[bil_no] [tinyint] NULL ,
[drv_num] [tinyint] NULL ,
[trm_num] [smallint] NULL ,
[bit_tarih] [smalldatetime] NULL ,
[bit_saat] [datetime] NULL ,
[test_deger] [float] NULL ,
[test_deger2] [float] NULL ,
[test_deger3] [float] NULL ,
[inoutno] [bigint] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[kalite_kontrol] (
[is_num] [bigint] NOT NULL ,
[oper_num] [int] NOT NULL ,
[oper_var] [smallint] NOT NULL ,
[hata] [tinyint] NULL ,
[saat] [datetime] NOT NULL ,
[tarih] [smalldatetime] NOT NULL ,
[kp_num] [bigint] NOT NULL ,
[kont_adet] [tinyint] NULL ,
[s_no] [bigint] IDENTITY (1, 1) NOT NULL ,
[kalite_tp] [tinyint] NOT NULL ,
[merr_code] [varchar] (10) COLLATE Turkish_CI_AS NOT NULL ,
[serr_code] [varchar] (10) COLLATE Turkish_CI_AS NOT NULL ,
[area_code] [varchar] (10) COLLATE Turkish_CI_AS NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[is_acma_bar] WITH NOCHECK ADD
CONSTRAINT [PK_is_bar] PRIMARY KEY CLUSTERED
(
[is_num],
[oper_num],
[oper_var]
) WITH FILLFACTOR = 50 ON [PRIMARY]
GO

ALTER TABLE [dbo].[kalite_kontrol] WITH NOCHECK ADD
CONSTRAINT [PK_kalite_kontrol] PRIMARY KEY CLUSTERED
(
[is_num],
[oper_num],
[oper_var],
[saat],
[tarih],
[kalite_tp],
[merr_code],
[serr_code]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [is_bar_tar] ON [dbo].[is_acma_bar]([tarih]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [is_bar_pers] ON [dbo].[is_acma_bar]([pers_num]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE UNIQUE INDEX [is_bar_bar] ON [dbo].[is_acma_bar]([barkod]) WITH IGNORE_DUP_KEY ON [PRIMARY]
GO

CREATE INDEX [is_bar_isnum] ON [dbo].[is_acma_bar]([is_num]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [is_bar_perstar] ON [dbo].[is_acma_bar]([pers_num], [tarih]) ON [PRIMARY]
GO

CREATE INDEX [IX_is_acma_bar_inoutno] ON [dbo].[is_acma_bar]([inoutno]) ON [PRIMARY]
GO

CREATE INDEX [IX_is_bar_oper] ON [dbo].[is_acma_bar]([oper_num]) ON [PRIMARY]
GO

CREATE INDEX [kalite_kont_ind] ON [dbo].[kalite_kontrol]([s_no]) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

CREATE INDEX [IX_kk_is_oper_var] ON [dbo].[kalite_kontrol]([is_num], [oper_num], [oper_var]) ON [PRIMARY]
GO

CREATE INDEX [IX_kk_is_tp] ON [dbo].[kalite_kontrol]([is_num], [kalite_tp]) ON [PRIMARY]
GO

CREATE INDEX [IX_kk_tar_tp_kpnum] ON [dbo].[kalite_kontrol]([tarih], [kalite_tp], [kp_num]) ON [PRIMARY]
GO

CREATE INDEX [IX_kk_tar] ON [dbo].[kalite_kontrol]([tarih]) ON [PRIMARY]
GO

CREATE INDEX [IX_kk_kal_tp] ON [dbo].[kalite_kontrol]([kalite_tp]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[kalite_kontrol] ADD
CONSTRAINT [FK_kalite_kontrol_ab_kk_err_area] FOREIGN KEY
(
[area_code]
) REFERENCES [dbo].[ab_kk_err_area] (
[area_code]
),
CONSTRAINT [FK_kalite_kontrol_ab_kk_err_type] FOREIGN KEY
(
[merr_code],
[serr_code],
[area_code]
) REFERENCES [dbo].[ab_kk_err_type] (
[merr_code],
[serr_code],
[area_code]
),
CONSTRAINT [FK_kalite_kontrol_ab_kk_main_err] FOREIGN KEY
(
[merr_code]
) REFERENCES [dbo].[ab_kk_main_err] (
[merr_code]
),
CONSTRAINT [FK_kalite_kontrol_ab_kk_sub_err] FOREIGN KEY
(
[merr_code],
[serr_code]
) REFERENCES [dbo].[ab_kk_sub_err] (
[merr_code],
[serr_code]
)
GO

 

[fredericofonseca]

and what exactly is the error you get?

Or you don't get any error but don't get any rows? - if this is the case that just means that either you don't have the data you require or that the joins and/or where clause is wrong. - note that in this case none of the indexes is of importance whatsoever

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[baran121]

Dear Fredericofonseca it doesnt give answer. i use SQL server down,
Microsoft SQL Server 2012 - 11.0.2218.0 (X64)
Jun 12 2012 13:05:25
Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

i noticed that it is not about joining i use query down, i couldnt get data.
select top 1 *from kalite_kontrol k where k.tarih='2016-03-23'

i rebuilt indexes but no changes.
Please need help. thank you.

 

[Olaf Doschke]

try

[tt]select top 1 * from kalite_kontrol k where k.tarih='20160323'[/tt]
and also
[tt]select top 1 * from kalite_kontrol[/tt]
besides, DO you have any data in the table at all? Browse it in SSMS or
[tt]select Count(*) from kalite_kontrol[/tt]

If you yourself executed the code to create tbale and indexes, you have an empty table. You can't pull any data with any query from an empty table.

Bye, Olaf.

 

[baran121]

hi
when i query down it gives result.
select top 1 * from kalite_kontrol

also select count(*) from kalite_kontrol --14368519 rows.

i also noticed that when i use the code down it gives result.
select *from kalite_kontrol WITH(INDEX(IX_kk_tar)) where tarih='2016-03-23'
but i already have this index on table. please can anyone help me about this problem.
thanks you.

 

[Olaf Doschke]

when i query down it gives result.
select top 1 * from kalite_kontrol

OK, your table isn't empty.

i also noticed that when i use the code down it gives result.
select *from kalite_kontrol WITH(INDEX(IX_kk_tar)) where tarih='2016-03-23'

OK, but you also say the following yields no result

select top 1 *from kalite_kontrol k where k.tarih='2016-03-23'

Again asked: What does this result?

select top 1 * from kalite_kontrol k where k.tarih='20160323'

Not using dashes makes a big difference in case LANGUAGE and DATEFORMAT settings let the server not accept a date with dashes.

Do you get any errors in the messages tab?

And what does SSMS tell about the execution plan with and without the WITH INDEX clause?
In SSMS use CTRL+M to let the actual execution pla be included, then do the query. Look at the tab "execution plan".

Bye, Olaf.

 

[baran121]

thank you for answering. Yes my table is not empty. it has 14368519 rows.
select count(*) from kalite_kontrol --14368519 rows.

when i use WITH(INDEX(IX_kk_tar)) it gives result but both of them same speed. when i dont use WITH(INDEX(IX_kk_tar)) it waits about half hour maybe more.

select *from kalite_kontrol WITH(INDEX(IX_kk_tar)) where tarih='2016-03-23'

select * from kalite_kontrol k WITH(INDEX(IX_kk_tar)) where k.tarih='20160323'

 

[Olaf Doschke]

What about the execution plan?
Do you get any errors in the messages tab?

Are the results using date with and without dashes the same, that means is the following result empty?

select * from kalite_kontrol WITH(INDEX(IX_kk_tar)) where tarih='2016-03-23'
except
select * from kalite_kontrol k WITH(INDEX(IX_kk_tar)) where k.tarih='20160323'

Bye, Olaf.

 

[baran121]

What about the execution plan?
i cheched what is the execution plan. then i found
SELECT * FROM sys.dm_db_missing_index_details WHERE [object_id] = OBJECT_ID('dbo.kalite_kontrol');
it doesnt give me inportant indexes

Do you get any errors in the messages tab?
i waited half hour or more with no result no error nothing. then i cancel execution plan.

 

[Olaf Doschke]

What are you talkling about? You can't cancel execution plan, you can only cancel execution.

The execution plan is generated when you check it by CTRL+M before you execute the query, then execute and wait, no matter how long it'll take.
You can't be impatient here and stop the execution, if you want to know what SQL Server does and why and whether it uses another index than when you force it and what to change.

OK, if you're that impatient you may first try the expected execution plan, but it may differ from the real executed plan. At least you won't need to wait for the query to end, write it, then press CTRL+L and see what index will most probably be used in what way. It won't help to know what IS executed, though.

If sys.dm_db_missing_index_details isn't returning rows SQL Server sees no problem. It's not very helpful.

Bye, Olaf.

 

[Olaf Doschke]

Without having any data my SQL Server tells me it uses the clustered index PK_kalite_control, of course that doesn't really speed up a WHERE tarih='20160323'.
Your clustered index is seemingly wrong and causing that lame performance. The clustered index nodes will have all columns anyway, no matter if you put all columns in the index definition or not, but it's the index by which data is really physically stored, clustered. You misuse that.

In the first place, remove the tarih column from the clustered index. Then the chances are high the better index is used to optimize the date lookup.

Bye, Olaf.

 

[baran121]

sorry i wanna say i cancel execution query.

i use CTRL + L , plan says to use index for tarih , so i deleted index which i use then i create index again (it is down) so now i dont need to use WITH(INDEX(IX_kk_tar)). strange but it works thank you.

CREATE NONCLUSTERED INDEX [IX_kk_tar]
ON [dbo].[kalite_kontrol] ([tarih])
INCLUDE ([is_num],[oper_num],[oper_var],[hata],[saat],[kp_num],[kont_adet],[s_no],[kalite_tp],[merr_code],[serr_code],[area_code])

 

[fredericofonseca]

can you also post the output of the following query. and let the query finish even if it takes hours!!!!

select top 2
       k.tarih
     , count(*)
from kalite_kontrol  k
where k.tarih between convert(smalldatetime, '2016-03-23 00:00:00', 120) and convert(smalldatetime, '2016-03-23 23:59:00', 120)
group by k.tarih
order by 1

also can you tell us how much memory your pc has and how many processors/cores

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[Olaf Doschke]

Well, that now shouldn't take hours anymore, since the newly created IX_kk_tar index works better. I don't see why you'd include all the other columns in the index nodes.

I don't know if you know what you do here, but judging by what you stuff in your clustered index: If you want to really gain profit from the clustered index you let it cluster data in the aspect you mainly query in, if you mainly have queries taking a certain time interval of data, then only put datetimes within the clustered index, additionally put the primary key in there and you have a unique value, otherwise SQL Server will make each node have a unique key.

Any nonclustered index of course will only lead to the pages in the physical storage. You gain by adding columns you also select, but in case you SELECT * you better not include all other fields, just to cover that. This thing about index optimization is a chapter of its own.

The way the new index helps you is having all needed columns and not needing to fetch data from the non optimal clustering, most probably, or the previous IX_kk_tar really was simply corrupted.

Were these indexes advised by SQL Server Profiling, perhaps? Then you may jave done that too early with to few data and queries on a too short timescale and the profiler recommended indexes ideal for that set of data and queries, but not in the long run. This is the curse of premature optimization.

Bye, Olaf.