Query Running Slow Help Needed

I have two parts of a query, which are causing it to run very slowly.

Is there another way I could rewrite these parts?

Part One - looks at the last fullmonth, the system starts at 1970 with regards to dates.

WHERE   Fieldname >= DATEDIFF(second, '1970-01-01', DATEADD(DAY, - DAY(GETDATE()) + 1, CONVERT(CHAR(8), DATEADD(MONTH, - 1, GETDATE()), 112))) AND Fieldname < DATEDIFF(second, '1970-01-01', DATEADD(DAY, - DAY(GETDATE()) + 1, CONVERT(CHAR(8), GETDATE(), 112)))

Part Two - second part of above Where statement.

Can you create an index on the field below and then drop it at the ned, or is there a better way of doing this as I think this is related to the field not being indexed

WHERE     callingPartyLoginUserID IN ( 'username1', 'username1', 'username3' etc)

Thanks

polynominalAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SjoerdVerweijCommented:
Part One:

Yes. Do

Declare @N Integer
Set @N = DATEDIFF(second, '1970-01-01', DATEADD(DAY, - DAY(GETDATE()) + 1, CONVERT(CHAR(8), DATEADD(MONTH, - 1, GETDATE()), 112)))

...

Where
  FieldName > @N ...

Part Two:

Yes, but why drop it at the end?



Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mcmonapCommented:
Hi polynominal,

Part One:

You could try the first like this:
WHERE   Fieldname >= DATEDIFF(second, '1970-01-01', DATEADD(DAY, - DAY(GETDATE()) + 1, CONVERT(CHAR(8), DATEADD(MONTH, - 1, GETDATE()), 112)))
             AND Fieldname < DATEDIFF(second, '1970-01-01', DATEADD(DAY, - DAY(GETDATE()) + 1, CONVERT(CHAR(8), GETDATE(), 112)))
Check out this page which is a good reference for SQL date calculations:
http://www.databasejournal.com/features/mssql/article.php/3076421
HOWEVER!  I doubt that having these calulations are cauing the query to slow down, since they are not data related they will calculate almost instantly.  You can prove this by adding the values to variables first, try running the first part of this code with just the variable selects, then do it with your query.  If it still takes a long time you know it is the data access taking a longtime not the argument calculations.  If it is the data then you might want to add indexes to the columns - possibly even clustered indexes depending on what else done with this table.

--Start Code

DECLARE @Date1 INT
DECLARE @Date2 INT

SET @Date1 = DATEDIFF(second, '1970-01-01', DATEADD(mm, -1, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)))
SET @Date2 = DATEDIFF(second, '1970-01-01', DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))

SELECT @Date1, @Date2

--...your query
WHERE   Fieldname >= @Date1
            AND Fieldname < @Date2

--End Code

Part Two:
It is not worth adding an index for one query the overhead would make it pointless, is a permanent index not an option?
mcmonapCommented:
umm, Part One should have read:

WHERE  Fieldname >= DATEDIFF(second, '1970-01-01', DATEADD(mm, -1, DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)))
      AND Fieldname < DATEDIFF(second, '1970-01-01', DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.