Datatype Conversions

The below query is causing too much wait time.

(@P0 bigint,@P1 bigint,@P2 bigint,@P3 bigint,@P4 varchar(4000))
select * from USER_TRACKED_EVENT where (USER_TRACKED_ID= @P0  AND USER_EVENT_TYPE_ID= @P1 )
OR (USER_TRACKED_ID= @P2  AND USER_EVENT_TYPE_ID= @P3  AND VALUE1= @P4 )

In case of Datatype mismatch, is it best option to convert @p4 or should we convert "VALUE1" column in USER_TRACKED_EVENT  table .
The datatype of "VALUE1"  is varchar (32).


I think it is best to convert the temporary variable @p4 but i dont want to miss any data, as if the data exceeds varchar(32) , the remaining will be trimmed.
But if we convert "value1" o varchar(8000), then it will utilize memory.
Please give some suggestion
omkaar4Asked:
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.

jogosCommented:
Always convert the variable. If you convert the column then even if it is indexed that index will be neglected.
0
Aneesh RetnakaranDatabase AdministratorCommented:
You need to give us your current table / index structures for a proper answer.


Hows is it running without the second condition

select * from USER_TRACKED_EVENT where (USER_TRACKED_ID= @P0  AND USER_EVENT_TYPE_ID= @P1 )
0
jogosCommented:
"I think it is best to convert the temporary variable @p4 but i dont want to miss any data, as if the data exceeds varchar(32) , the remaining will be trimmed."

How can a column that is a varchar(32) be equal to a value that has a length of more than 32 characters?
Two varchars are not a type mismatch.
0
jogosCommented:
Hope you have an index on USER_TRACKED_ID and USER_EVENT_TYPE (and eventualy VALUE1).
You use SELECT * so you need to access the table. If you only need a few columns from a lot then you can you maybe can create an index that includes that few columns.
Update statistics and index maintenance is always a good to check before you look for other things

What for wait time?
http://www.sqlskills.com/blogs/paul/advanced-performance-troubleshooting-waits-latches-spinlocks/
0
Scott PletcherSenior DBACommented:
For the purposes of this query, USER_TRACKED_EVENT needs clustered by USER_TRACKED_ID and USER_EVENT_TYPE_ID (in whichever order is most appropriate/most sequential).  That's especially true if you most often/almost always search that table using those column values.  If, despite that, the table is clustered on IDENTITY, CHANGE THE CLUSTERING KEY ASAP!  Mindlessly clustering on IDENTITY is the single most common performance mistake!

The P4/value1 is very likely not the cause of your performance issue.
0

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
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.