mister.magoo (10/12/2012)

---

Here is my sample data creation: 10,000,000 + rows in tempdb - you may want to put it somewhere else...it's about 1GB of data.

Oh, and it relies on a...

Jeff Moden (10/11/2012)

---

Is...

I guess the sittuation is not so difficult to him anymore 🙂 oh well....

On the side note, GilaMonster in indonesian it means "Crazy Monster" as Gila=Crazy what you mean?

How about this

--test ddl

declare @sales_log table(member_id varchar(50),company_id varchar(50),purchase_dt datetime,purchase_amt money)

insert into @sales_log(member_id,company_id,purchase_dt,purchase_amt)

select 'Jen9921', '222141','1/2/2011','11.99' union all

select 'Jen9921', '222141','2/2/2011','11.99' union all

select 'Jen9921', '222141','3/2/2011','11.99' union all

select 'Jen9921', '222141','4/2/2011','11.99'...

I upload new execution plan . if someone can help identify one more break thru like MM did would be a home run for me.

Thanks in advance.

mister.magoo (10/11/2012)

---

haiao2000 (10/11/2012)

---

Maybe this will give another angle to look at. There are 2 ways I can benefit from this query if I can get it runs fast enough.

1) get...

Maybe this will give another angle to look at. There are 2 ways I can benefit from this query if I can get it runs fast enough.

1) get all the...

MM,

I switched to clustered Index it runs about the same amount of time. Could you think of something else I can try to see if it makes any different? If...

MM

Here it is

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time =...

MM,

Here it is

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time...

Here it is.

SQL Server parse and compile time:

CPU time = 0 ms, elapsed time = 0 ms.

SQL Server Execution Times:

CPU time =...

MM,

IT runs even slower in another box. See attachment

Looks like it used the wrong index key. there is a clustered index that combined( ParentID, ChildId, and 2 other fields)

While there...

MM

Attached is new plan running on permanent table sitting on a separated database (in the same laptop).

I will run off and run it on another box and see if anything...

I see, I suspect that my laptop runs slower than a normal one, so I will test on another machine later. I am going to create a permanentable senarios...

MM,

Sorry here it is. This one has 4 levels. With 34,689 records return in 53 seconds. The table has 10,831,321 records

Updated: uploaded screenshot of segment that...