Optimize SQL

Question

Optimize SQL

Viewing 6 posts - 31 through 35 (of 35 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

J Livingston SQL (7/3/2014)
Lynn Pettis (7/3/2014)
J Livingston SQL (7/3/2014)
.
I agree. With 3,250,325 rows in the main table mine ran in about 3 seconds.
what spec hardware you running Lynn.....?

Dell M4600 laptop, dual CORE i7 with 4 core each, 8GB RAM total, 2GB max setting for SQL Server 2012. Someone told me it has an SSD drive but I'm not too sure about that. Haven't dug into it as it is my work laptop.

[/quote]

hehe...that explains why my single 4 core I5 and 4gb Ram (32bit os) wasn't achieving 3 secs like you...best I could get was around 7/8 secs :-P[/quote]

Oh, yes, mine is also a 64-bit system.

Jeff Moden SSC Guru Points: 1004392 More actions · Answer 2

I love being the one with the slowest machine on the block. It means that when I write something that run fast on it, it's usually going to smoke the competition on bigger machines.

Even with that, I'm still amazed at the speed of my relatively small i5/64. HUGE difference from the 32 bit world.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Lynn Pettis

SSC Guru

Points: 442467

July 11, 2014 at 8:24 am

#1728767

To the OP, any updates?

Arno Ho SSCommitted Points: 1662 More actions · Answer 4

Hi,

Your algorithm is faster than mine but it does not fully meet my expectations

We need to calculate the hours scheduled and that is really the hours worked by each agent.

-> 11/07/2014 the agent has 7h45 to do and really did 7h32

With the current method I calculated once the 2 numbers (expected and realized).

With the proposed method, I do two queries: one for expected and for real

Observing customer data I found that the share of days and forced absences were very low: 5%. But this systematic calculation consumed much time.

1 / I added a field to the table officers to indicate those constraints holders and for those with absences

2 / I will update this field (a few seconds)

3 / I loop agent (too complex to review the whole process in the state)

I calculate for each

3.1/ expected for daily attendance. I record the results (expected , real= expected )

3.2/ if the agent has constraints then I calculate the scheduled day forced. I update (column expected=new expected, real=new expected)

3.3/ if the agent absences, I realized the Cacule for days of absence. I update (column real only)

It's divise the SQL time by 2 and there's less impact on the log

Thank's for your help

Arno

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Arno Ho (7/11/2014)
Hi,
Your algorithm is faster than mine but it does not fully meet my expectations
We need to calculate the hours scheduled and that is really the hours worked by each agent.
-> 11/07/2014 the agent has 7h45 to do and really did 7h32
With the current method I calculated once the 2 numbers (expected and realized).
With the proposed method, I do two queries: one for expected and for real
Observing customer data I found that the share of days and forced absences were very low: 5%. But this systematic calculation consumed much time.
1 / I added a field to the table officers to indicate those constraints holders and for those with absences
2 / I will update this field (a few seconds)
3 / I loop agent (too complex to review the whole process in the state)
    I calculate for each

    3.1/ expected for daily attendance. I record the results (expected , real= expected )
    3.2/ if the agent has constraints then I calculate the scheduled day forced. I update (column expected=new expected, real=new expected)
    3.3/ if the agent absences, I realized the Cacule for days of absence. I update (column real only)
It's divise the SQL time by 2 and there's less impact on the log
Thank's for your help
Arno

Not exactly sure how it doesn't meet your requirements other than I don't display the always expected 450 minutes. That is actually a static value if there are no absences based on the data you provided. The rest you state above doesn't appear to be anything you specified in your requirements.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 6

Arno Ho (7/11/2014)
Hi,
Your algorithm is faster than mine but it does not fully meet my expectations
Arno

Arno...did you run your script against the test data script I provided....what were the timing differences?

can you please clarify your expectations based on the test data script I provided...if the test script doesn't replicate your real data than either please provide a suitable one or I will try to adapt as you require

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day