Help finding record pairs

I'm trying to find away to match up two rows into a single event.
PK Name Event Type
1 Foo Open Foreground
8 Foo Close Foreground
16 Bar Open Foreground
18 Bar Open Background
22 Bar Close Background
23 Bar Cose Foreground
I need to know that
1 is paired with 8
16 is paired with 23
18 is paired with 22
Any help would be appreciatedPlease post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are.
This looks like a common design error. Time is based on durations
(Zeno, Einstein, et al) and not on split facts like you are showing.
Also, your data element names are much too vague for any data model.
The kludge to repair the design flaw will involve self-joins and
aggregations. It has been posted several times in various forms.
I think that you wanted something like this in the first place, instead
of wasting insane amounts of time constructing the proper design from
scratch everytime you use it.
CREATE TABLE Events
(event_name CHAR(3) NOT NULL,
open_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
close_time DATETIME, --null means still open
CHECK (open_time < close_time),
event_type CHAR(10) NOT NULL
CHECK (event_type IN ('Foreground', 'Background')),
PRIMARY KEY (event_name, open_time));|||"lee_mre@..no-spam.yahoo.com" <lee_mre@.yahoo.com> wrote in message
news:1134010863.875294.231700@.o13g2000cwo.googlegroups.com...
> I'm trying to find away to match up two rows into a single event.
>
> PK Name Event Type
> 1 Foo Open Foreground
> 8 Foo Close Foreground
> 16 Bar Open Foreground
> 18 Bar Open Background
> 22 Bar Close Background
> 23 Bar Cose Foreground
>
> I need to know that
> 1 is paired with 8
> 16 is paired with 23
> 18 is paired with 22
> Any help would be appreciated
>
create table T
(
PK int primary key,
Name varchar(5),
Event varchar(5),
Type varchar(20)
)
insert into T(PK,Name,Event,Type)
select 1, 'Foo', 'Open', 'Foreground'
union all
select 8, 'Foo', 'Close', 'Foreground'
union all
select 16, 'Bar', 'Open', 'Foreground'
union all
select 18, 'Bar', 'Open', 'Background'
union all
select 22, 'Bar', 'Close', 'Background'
union all
select 23, 'Bar', 'Close', 'Foreground'
select
o.pk Opened, c.pk Closed
from
T o
join T c
on o.Name = c.Name
and o.Event = 'Open'
and c.Event = 'Close'
and o.Type = c.Type
--BUT I suspect that you really want to match each open event with the
"next" close event of the same type. Assuming that "next" means next ORDER
BY PK.
select
o.pk Opened,
(select min(pk)
from T
where Event = 'Close'
and Type = o.Type
and pk > o.pk) Closed
from T o
where Event = 'Open'
--David|||On 7 Dec 2005 19:50:22 -0800, --CELKO-- wrote:

> CREATE TABLE Events
> (event_name CHAR(3) NOT NULL,
> open_time DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
> close_time DATETIME, --null means still open
> CHECK (open_time < close_time),
> event_type CHAR(10) NOT NULL
> CHECK (event_type IN ('Foreground', 'Background')),
> PRIMARY KEY (event_name, open_time));
Question ... how will the check constraint work when close_time is null on
an insert? Perhaps it should be
CHECK (NOT open_time >= close_time)
?|||On Thu, 8 Dec 2005 12:07:47 -0500, Ross Presser wrote:

>On 7 Dec 2005 19:50:22 -0800, --CELKO-- wrote:
>
>Question ... how will the check constraint work when close_time is null on
>an insert? Perhaps it should be
> CHECK (NOT open_time >= close_time)
>?
Hi Ross,
Not necessary. If close_time is NULL and open_time is '2005-12-10', then
the CHECK constraint suggested by Celko reads
CHECK (open_time < close_time)
after substitution
CHECK ('2005-12-10' < NULL)
which evaluates to
CHECK (Unknown)
A major difference between a logic test in a WHERE and a logic test in a
constraint is that a WHERE includes only rows if the test evaluates to
True (i.e. False and Unknown are both rejected), but a constraint
accepts rows if the test evaluates to either True or False (or, to
paraphrase the ANSI standard, the constraint must not evaluate to
False).
The row with NULL close_time will be allowed.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||
Hugo Kornelis wrote:
<snip>

>A major difference between a logic test in a WHERE and a logic test in a
>constraint is that a WHERE includes only rows if the test evaluates to
>True (i.e. False and Unknown are both rejected), but a constraint
>accepts rows if the test evaluates to either True or False (or, to
>
I think you meant to write "... if the test evaluates to either True or
Unknown ..."
SK

>paraphrase the ANSI standard, the constraint must not evaluate to
>False).
>
>|||On Sat, 10 Dec 2005 20:16:22 -0500, Steve Kass wrote:

>
>Hugo Kornelis wrote:
><snip>
>
>I think you meant to write "... if the test evaluates to either True or
>Unknown ..."
Hi Steve,
You're right. I obviously typed to fast and forgot to check before
posting.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql

help create query against two tables

stepdefinition has
steptype
flowid
stepid

task has
taskid
flowid
stepid

flowid and stepid for both tables match; meaning that if i found a record in task with a certain taskid, i could query stepdefinition with the same flowid and stepid to find the steptype.

well, i wanna do it the other way around. I query stepdefinition to find a list of flowids and stepids for a specific steptype.

select flowid, stepid from stepdefinition where steptype = -3

Now, I want to find all taskids in task for each flowid/stepid combination

here's a visual

http://www.filecabin.com/up1/1144249279-task.gifselect taskid from task
where stepID+flowID in
(select stepID+flowID from stepdefinition where steptype = -3)
order by taskid

works|||select t.taskid, t.stepID, t.flowID
from task as t
join stepdefinition as s
on ( t.stepID = s.stepID
and t.flowID = s.flowID )
where s.steptype = -3

Help - Index Problem

Hi all
I have a table which have four index. And I want to run a query which have the criteria to match the index (my_index_4). However when i passed different value to the query, the speed of the query will be different. Sometimes, the query cannot hit the index. The details as follows
Table : mytable (about 900,000 records with no clustered index index
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date + sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date
Index : my_index_3 ( company_code + order_no + order_date
Index : my_index_4 ( company_code + order_type + order_date
*Query 1: select * from mytable where company_code='100' and order_type='ABC' and order_date between '2003-01-01' and '2003-12-31
*Query 2: select * from mytable where company_code='100' and order_type='ABC' and order_date between '2002-01-01' and '2002-12-31
Query 3: select * from mytable where company_code='100' and order_type='ABC' and order_date between '2001-01-01' and '2001-12-31
Query 4: select * from mytable where company_code='100' and order_type='ABC' and order_date between '2000-01-01' and '2000-12-31
Query 5: select * from mytable where company_code='100' and order_type='ABC' and order_date between '1999-01-01' and '1999-12-31
Query 6: select * from mytable where company_code='100' and order_type='ABC' and order_date between '1998-01-01' and '1998-12-31
Query 7: select * from mytable where company_code='100' and order_type='ABC' and order_date between '1998-01-01' and '1998-12-31
*Query 8: select * from mytable where company_code='100' and order_type='ABC' and order_date between '1997-01-01' and '1997-12-31
Query 1, 2& 5 take about 2 seconds. However, other queries take more than 18 seconds. I found that those queries did not hit the index. Therefore, it used table scan. Can you tell me why some queries did not use the index (my_index_4). I tried to re-built the index, and update statistic and it still have the same result.
Then I rewrite the query using the "index=" to force it use the index. Then all queries (Query 9-12)take only 2 seconds
Query 9: select * from mytable (index=my_index_4) where company_code='100' and order_type='ABC' and order_date between '2003-01-01' and '2003-12-31
Query 10: select * from mytable (index=my_index_4) where company_code='100' and order_type='ABC' and order_date between '2000-01-01' and '2000-12-31
Query 11: select * from mytable (index=my_index_4) where company_code='100' and order_type='ABC' and order_date between '1997-01-01' and '1997-12-31
There are about 5000 records for each query (one year for one company)
Thanks
SimoSimon
Why did you create indexes on columns which kave already covered by another
index?
Look at this article helps you
http://www.sql-server-performance.com/covering_indexes.asp
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:B56FF5D7-F1F6-49B4-AF9A-2765190C8D2D@.microsoft.com...
> Hi all,
> I have a table which have four index. And I want to run a query which
have the criteria to match the index (my_index_4). However when i passed
different value to the query, the speed of the query will be different.
Sometimes, the query cannot hit the index. The details as follows.
> Table : mytable (about 900,000 records with no clustered index index)
> Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date
+ sequence_no)
> Index : my_index_2 ( company_code + cutomer_no + order_date)
> Index : my_index_3 ( company_code + order_no + order_date)
> Index : my_index_4 ( company_code + order_type + order_date)
> *Query 1: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'
> *Query 2: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2002-01-01' and '2002-12-31'
> Query 3: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2001-01-01' and '2001-12-31'
> Query 4: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
> Query 5: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1999-01-01' and '1999-12-31'
> Query 6: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
> Query 7: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
> *Query 8: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
> Query 1, 2& 5 take about 2 seconds. However, other queries take more than
18 seconds. I found that those queries did not hit the index. Therefore, it
used table scan. Can you tell me why some queries did not use the index
(my_index_4). I tried to re-built the index, and update statistic and it
still have the same result.
> Then I rewrite the query using the "index=" to force it use the index.
Then all queries (Query 9-12)take only 2 seconds.
> Query 9: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '2003-01-01'
and '2003-12-31'
> Query 10: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '2000-01-01'
and '2000-12-31'
> Query 11: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '1997-01-01'
and '1997-12-31'
> There are about 5000 records for each query (one year for one company).
> Thanks,
> Simon
>|||Also make sure the index statistics are up to date... It really sounds to me
like some of the queries return more records than others, which could cause
SQL to to a table scan... Use Query ANalyzer and look at the plan to see if
it changes between index seek and index scan...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:B56FF5D7-F1F6-49B4-AF9A-2765190C8D2D@.microsoft.com...
> Hi all,
> I have a table which have four index. And I want to run a query which
have the criteria to match the index (my_index_4). However when i passed
different value to the query, the speed of the query will be different.
Sometimes, the query cannot hit the index. The details as follows.
> Table : mytable (about 900,000 records with no clustered index index)
> Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date
+ sequence_no)
> Index : my_index_2 ( company_code + cutomer_no + order_date)
> Index : my_index_3 ( company_code + order_no + order_date)
> Index : my_index_4 ( company_code + order_type + order_date)
> *Query 1: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'
> *Query 2: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2002-01-01' and '2002-12-31'
> Query 3: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2001-01-01' and '2001-12-31'
> Query 4: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
> Query 5: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1999-01-01' and '1999-12-31'
> Query 6: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
> Query 7: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
> *Query 8: select * from mytable where company_code='100' and
order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
> Query 1, 2& 5 take about 2 seconds. However, other queries take more than
18 seconds. I found that those queries did not hit the index. Therefore, it
used table scan. Can you tell me why some queries did not use the index
(my_index_4). I tried to re-built the index, and update statistic and it
still have the same result.
> Then I rewrite the query using the "index=" to force it use the index.
Then all queries (Query 9-12)take only 2 seconds.
> Query 9: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '2003-01-01'
and '2003-12-31'
> Query 10: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '2000-01-01'
and '2000-12-31'
> Query 11: select * from mytable (index=my_index_4) where
company_code='100' and order_type='ABC' and order_date between '1997-01-01'
and '1997-12-31'
> There are about 5000 records for each query (one year for one company).
> Thanks,
> Simon
>|||> why some queries did not use the index (my_index_4). I tried to re-built
the index, and
> update statistic and it still have the same result.
Try using sample all of the data when updating the statistics.
Use the QA to do one of your queries.
select the query plan
rightclick the table
select manage statistics
Select the concerning statistic
Update
Select 'Sample all of the data'
This might give you better statistics then the defaults.
In one case were I studied a strange behaviour in my database,
concerning statistics it did work. It might work for your situation.
As said might, for my case I never figured out why with some
parameters the querie was different than with other parameters.
Updating the statistics with all of the data did the trick.
Please report if this does work in your situation.
Or any other solution that you find.
ben brugman|||Sorry, wongly typed. The my_index_1 should be
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_no + sequence_no)
================================================================= Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date + sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_4 ( company_code + order_type + order_date)
-- Simon wrote: --
Hi all,
I have a table which have four index. And I want to run a query which have the criteria to match the index (my_index_4). However when i passed different value to the query, the speed of the query will be different. Sometimes, the query cannot hit the index. The details as follows.
Table : mytable (about 900,000 records with no clustered index index)
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date + sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_4 ( company_code + order_type + order_date)
*Query 1: select * from mytable where company_code='100' and order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'
*Query 2: select * from mytable where company_code='100' and order_type='ABC' and order_date between '2002-01-01' and '2002-12-31'
Query 3: select * from mytable where company_code='100' and order_type='ABC' and order_date between '2001-01-01' and '2001-12-31'
Query 4: select * from mytable where company_code='100' and order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
Query 5: select * from mytable where company_code='100' and order_type='ABC' and order_date between '1999-01-01' and '1999-12-31'
Query 6: select * from mytable where company_code='100' and order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
Query 7: select * from mytable where company_code='100' and order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'
*Query 8: select * from mytable where company_code='100' and order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
Query 1, 2& 5 take about 2 seconds. However, other queries take more than 18 seconds. I found that those queries did not hit the index. Therefore, it used table scan. Can you tell me why some queries did not use the index (my_index_4). I tried to re-built the index, and update statistic and it still have the same result.
Then I rewrite the query using the "index=" to force it use the index. Then all queries (Query 9-12)take only 2 seconds.
Query 9: select * from mytable (index=my_index_4) where company_code='100' and order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'
Query 10: select * from mytable (index=my_index_4) where company_code='100' and order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
Query 11: select * from mytable (index=my_index_4) where company_code='100' and order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
There are about 5000 records for each query (one year for one company).
Thanks,
Simon|||I try to re-built the index, update the stat, and do almost everything (etc .dbcc.....). But all are failure.
But now, the problem is solved. The solution is to change the primary key to clustered index, and then re-built other index. Anyway, thanks all of your help.
Cheers,
Simon
-- ben brugman wrote: --
> why some queries did not use the index (my_index_4). I tried to re-built
the index, and
> update statistic and it still have the same result.
Try using sample all of the data when updating the statistics.
Use the QA to do one of your queries.
select the query plan
rightclick the table
select manage statistics
Select the concerning statistic
Update
Select 'Sample all of the data'
This might give you better statistics then the defaults.
In one case were I studied a strange behaviour in my database,
concerning statistics it did work. It might work for your situation.
As said might, for my case I never figured out why with some
parameters the querie was different than with other parameters.
Updating the statistics with all of the data did the trick.
Please report if this does work in your situation.
Or any other solution that you find.
ben brugman

Help - Index Problem

Hi all,
I have a table which have four index. And I want to run a query which have
the criteria to match the index (my_index_4). However when i passed differen
t value to the query, the speed of the query will be different. Sometimes, t
he query cannot hit the ind
ex. The details as follows.
Table : mytable (about 900,000 records with no clustered index index)
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date +
sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_4 ( company_code + order_type + order_date)
*Query 1: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '2003-01-01' and '2003-12-31'
*Query 2: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '2002-01-01' and '2002-12-31'
Query 3: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '2001-01-01' and '2001-12-31'
Query 4: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '2000-01-01' and '2000-12-31'
Query 5: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1999-01-01' and '1999-12-31'
Query 6: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1998-01-01' and '1998-12-31'
Query 7: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1998-01-01' and '1998-12-31'
*Query 8: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '1997-01-01' and '1997-12-31'
Query 1, 2& 5 take about 2 seconds. However, other queries take more than 18
seconds. I found that those queries did not hit the index. Therefore, it us
ed table scan. Can you tell me why some queries did not use the index (my_in
dex_4). I tried to re-buil
t the index, and update statistic and it still have the same result.
Then I rewrite the query using the "index=" to force it use the index. Then
all queries (Query 9-12)take only 2 seconds.
Query 9: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'
Query 10: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
Query 11: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
There are about 5000 records for each query (one year for one company).
Thanks,
SimonSimon
Why did you create indexes on columns which kave already covered by another
index?
Look at this article helps you
http://www.sql-server-performance.c...ing_indexes.asp
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:B56FF5D7-F1F6-49B4-AF9A-2765190C8D2D@.microsoft.com...

quote:

---

> Hi all,
> I have a table which have four index. And I want to run a query which

---

have the criteria to match the index (my_index_4). However when i passed
different value to the query, the speed of the query will be different.
Sometimes, the query cannot hit the index. The details as follows.

quote:

---

> Table : mytable (about 900,000 records with no clustered index index)
> Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date

---

+ sequence_no)

quote:

---

> Index : my_index_2 ( company_code + cutomer_no + order_date)
> Index : my_index_3 ( company_code + order_no + order_date)
> Index : my_index_4 ( company_code + order_type + order_date)
> *Query 1: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'

quote:

---

> *Query 2: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '2002-01-01' and '2002-12-31'

quote:

---

> Query 3: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '2001-01-01' and '2001-12-31'

quote:

---

> Query 4: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'

quote:

---

> Query 5: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '1999-01-01' and '1999-12-31'

quote:

---

> Query 6: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'

quote:

---

> Query 7: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'

quote:

---

> *Query 8: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'

quote:

---

> Query 1, 2& 5 take about 2 seconds. However, other queries take more than

---

18 seconds. I found that those queries did not hit the index. Therefore, it
used table scan. Can you tell me why some queries did not use the index
(my_index_4). I tried to re-built the index, and update statistic and it
still have the same result.

quote:

---

> Then I rewrite the query using the "index=" to force it use the index.

---

Then all queries (Query 9-12)take only 2 seconds.

quote:

---

> Query 9: select * from mytable (index=my_index_4) where

---

company_code='100' and order_type='ABC' and order_date between '2003-01-01'
and '2003-12-31'

quote:

---

> Query 10: select * from mytable (index=my_index_4) where

---

company_code='100' and order_type='ABC' and order_date between '2000-01-01'
and '2000-12-31'

quote:

---

> Query 11: select * from mytable (index=my_index_4) where

---

company_code='100' and order_type='ABC' and order_date between '1997-01-01'
and '1997-12-31'

quote:

---

> There are about 5000 records for each query (one year for one company).
> Thanks,
> Simon
>

---

|||Also make sure the index statistics are up to date... It really sounds to me
like some of the queries return more records than others, which could cause
SQL to to a table scan... Use Query ANalyzer and look at the plan to see if
it changes between index seek and index scan...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon" <anonymous@.discussions.microsoft.com> wrote in message
news:B56FF5D7-F1F6-49B4-AF9A-2765190C8D2D@.microsoft.com...

quote:

---

> Hi all,
> I have a table which have four index. And I want to run a query which

---

have the criteria to match the index (my_index_4). However when i passed
different value to the query, the speed of the query will be different.
Sometimes, the query cannot hit the index. The details as follows.

quote:

---

> Table : mytable (about 900,000 records with no clustered index index)
> Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date

---

+ sequence_no)

quote:

---

> Index : my_index_2 ( company_code + cutomer_no + order_date)
> Index : my_index_3 ( company_code + order_no + order_date)
> Index : my_index_4 ( company_code + order_type + order_date)
> *Query 1: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'

quote:

---

> *Query 2: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '2002-01-01' and '2002-12-31'

quote:

---

> Query 3: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '2001-01-01' and '2001-12-31'

quote:

---

> Query 4: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'

quote:

---

> Query 5: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '1999-01-01' and '1999-12-31'

quote:

---

> Query 6: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'

quote:

---

> Query 7: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '1998-01-01' and '1998-12-31'

quote:

---

> *Query 8: select * from mytable where company_code='100' and

---

order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'

quote:

---

> Query 1, 2& 5 take about 2 seconds. However, other queries take more than

---

18 seconds. I found that those queries did not hit the index. Therefore, it
used table scan. Can you tell me why some queries did not use the index
(my_index_4). I tried to re-built the index, and update statistic and it
still have the same result.

quote:

---

> Then I rewrite the query using the "index=" to force it use the index.

---

Then all queries (Query 9-12)take only 2 seconds.

quote:

---

> Query 9: select * from mytable (index=my_index_4) where

---

company_code='100' and order_type='ABC' and order_date between '2003-01-01'
and '2003-12-31'

quote:

---

> Query 10: select * from mytable (index=my_index_4) where

---

company_code='100' and order_type='ABC' and order_date between '2000-01-01'
and '2000-12-31'

quote:

---

> Query 11: select * from mytable (index=my_index_4) where

---

company_code='100' and order_type='ABC' and order_date between '1997-01-01'
and '1997-12-31'

quote:

---

> There are about 5000 records for each query (one year for one company).
> Thanks,
> Simon
>

---

|||> why some queries did not use the index (my_index_4). I tried to re-built
the index, and

quote:

---

> update statistic and it still have the same result.

---

Try using sample all of the data when updating the statistics.
Use the QA to do one of your queries.
select the query plan
rightclick the table
select manage statistics
Select the concerning statistic
Update
Select 'Sample all of the data'
This might give you better statistics then the defaults.
In one case were I studied a strange behaviour in my database,
concerning statistics it did work. It might work for your situation.
As said might, for my case I never figured out why with some
parameters the querie was different than with other parameters.
Updating the statistics with all of the data did the trick.
Please report if this does work in your situation.
Or any other solution that you find.
ben brugman|||Sorry, wongly typed. The my_index_1 should be
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_no + se
quence_no)
========================================
=========================
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date +
sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_4 ( company_code + order_type + order_date)
-- Simon wrote: --
Hi all,
I have a table which have four index. And I want to run a query which have
the criteria to match the index (my_index_4). However when i passed differen
t value to the query, the speed of the query will be different. Sometimes, t
he query cannot hit th
e index. The details as follows.
Table : mytable (about 900,000 records with no clustered index index)
Index : my_index_1 ( Primary Key - company_code + cutomer_no + order_date +
sequence_no)
Index : my_index_2 ( company_code + cutomer_no + order_date)
Index : my_index_3 ( company_code + order_no + order_date)
Index : my_index_4 ( company_code + order_type + order_date)
*Query 1: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '2003-01-01' and '2003-12-31'
*Query 2: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '2002-01-01' and '2002-12-31'
Query 3: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '2001-01-01' and '2001-12-31'
Query 4: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '2000-01-01' and '2000-12-31'
Query 5: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1999-01-01' and '1999-12-31'
Query 6: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1998-01-01' and '1998-12-31'
Query 7: select * from mytable where company_code='100' and order_type='AB
C' and order_date between '1998-01-01' and '1998-12-31'
*Query 8: select * from mytable where company_code='100' and order_type='A
BC' and order_date between '1997-01-01' and '1997-12-31'
Query 1, 2& 5 take about 2 seconds. However, other queries take more than 18
seconds. I found that those queries did not hit the index. Therefore, it us
ed table scan. Can you tell me why some queries did not use the index (my_in
dex_4). I tried to re
-built the index, and update statistic and it still have the same result.
Then I rewrite the query using the "index=" to force it use the index. Then
all queries (Query 9-12)take only 2 seconds.
Query 9: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '2003-01-01' and '2003-12-31'
Query 10: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '2000-01-01' and '2000-12-31'
Query 11: select * from mytable (index=my_index_4) where company_code='100'
and order_type='ABC' and order_date between '1997-01-01' and '1997-12-31'
There are about 5000 records for each query (one year for one company).
Thanks,
Simon|||I try to re-built the index, update the stat, and do almost everything (etc
.dbcc.....). But all are failure.
But now, the problem is solved. The solution is to change the primary key t
o clustered index, and then re-built other index. Anyway, thanks all of you
r help.
Cheers,
Simon
-- ben brugman wrote: --

quote:

---

> why some queries did not use the index (my_index_4). I tried to re-built

---

the index, and

quote:

---

> update statistic and it still have the same result.

---

Try using sample all of the data when updating the statistics.
Use the QA to do one of your queries.
select the query plan
rightclick the table
select manage statistics
Select the concerning statistic
Update
Select 'Sample all of the data'
This might give you better statistics then the defaults.
In one case were I studied a strange behaviour in my database,
concerning statistics it did work. It might work for your situation.
As said might, for my case I never figured out why with some
parameters the querie was different than with other parameters.
Updating the statistics with all of the data did the trick.
Please report if this does work in your situation.
Or any other solution that you find.
ben brugman