September 24, 2014 at 11:12 am
Hi!
I have a table with two columns, one of int type and another of varchar type.
On some clients the int column is used, on anothers in which the int column can not be used, the varchar column is used.
Should I merge both columns in just one varchar column? Will be the search performance affected too much?
I have indexes for each column.
Regards
September 24, 2014 at 12:00 pm
It really depends on how the optimizer is resolving those queries. It's also dependent on how you're writing the queries. AND, it's dependent on the indexes you have on the columns.
Assuming up to date statistics, well written queries, and the values are the same, probably, you won't see any real difference between the two at all. But that's a bunch of assumptions.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2014 at 12:24 pm
Grant Fritchey (9/24/2014)
... It's also dependent on how you're writing the queries. ... But that's a bunch of assumptions.
Big understatement on the quality of queries there.
One could bring a server to its knees with implicit conversions. Combining int with string, if doing joins on those values, or a parameter thinking int instead of string and it could slow down pretty significantly.
So yeah, I place a big importance on quality of queries.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 24, 2014 at 3:46 pm
Thanks for yours answers.
I thought the search by int column was faster, so meanwhile I also performed some tests on a table with a million records.
The values of columnString were the same as column int, but converted to string.
The difference that I found was a few mileseconds (in elapsed time)
The queries are otimized. They look like the following:
declare @int int = 123;
select column1,column2 from table where colInt = @int
With a non clustered index on colInt and column1, column2 included.
declare @string varchar(50) = '123';
select column1,column2 from table where colString = @string
With a non clustered index on colString and column1, column2 included.
An index seek was performed for each one of the options and sometimes the search by the string was faster a few milisecs.
I suppose it will only be slower when index on colString does not exists or turns too fragmented, but the same would occur for the search by int column I think.
Regards
September 24, 2014 at 4:25 pm
amns (9/24/2014)
Thanks for yours answers.I thought the search by int column was faster, so meanwhile I also performed some tests on a table with a million records.
The values of columnString were the same as column int, but converted to string.
The difference that I found was a few mileseconds (in elapsed time)
The queries are otimized. They look like the following:
declare @int int = 123;
select column1,column2 from table where colInt = @int
With a non clustered index on colInt and column1, column2 included.
declare @string varchar(50) = '123';
select column1,column2 from table where colString = @string
With a non clustered index on colString and column1, column2 included.
An index seek was performed for each one of the options and sometimes the search by the string was faster a few milisecs.
I suppose it will only be slower when index on colString does not exists or turns too fragmented, but the same would occur for the search by int column I think.
Regards
Now you should also look at the size difference between the two tables to see if there is much difference.
And one more test for performance.
declare @string varchar(50) = 123;
select column1,column2 from table where colString = @string
This should cause an implicit conversion and wouldn't be far fetched when somebody knows they are searching for an integer value in the column.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 25, 2014 at 2:37 am
SQLRNNR (9/24/2014)
And one more test for performance.
declare @string varchar(50) = 123;
select column1,column2 from table where colString = @string
This should cause an implicit conversion and wouldn't be far fetched when somebody knows they are searching for an integer value in the column.
That will not cause an implicit performance problem (assuming colString really is a string) as you're comparing a string column to a variable defined as varchar.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2014 at 2:53 am
Now you should also look at the size difference between the two tables to see if there is much difference.
Sorry, but I didn't understand what you mean. The table is the same, as the results.
Performance results for several options:
declare @int int = 123;
select column1,column2 from table where colInt = @int
(1 row(s) affected)
logical reads 7
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 24 ms.
declare @string varchar(50) = '123';
select column1,column2 from table where colString = @string
(1 row(s) affected)
logical reads 7
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 34 ms.
declare @string varchar(50) = 123;
select column1,column2 from table where colString = @string
logical reads 7
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 22 ms.
select column1,column2 from table where colString = 123
(1 row(s) affected)
This caused in implicit conversion.
logical reads 25599
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 113 ms.
Regards!
September 25, 2014 at 3:14 am
amns (9/25/2014)
Performance results for several options:
declare @int int = 123;
select column1,column2 from table where colInt = @int
(1 row(s) affected)
logical reads 7
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 24 ms.
declare @string varchar(50) = '123';
select column1,column2 from table where colString = @string
(1 row(s) affected)
logical reads 7
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 34 ms.
declare @string varchar(50) = 123;
select column1,column2 from table where colString = @string
logical reads 7
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 22 ms.
So no differences in performance worth worrying about.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 25, 2014 at 7:18 am
GilaMonster (9/25/2014)
SQLRNNR (9/24/2014)
And one more test for performance.
declare @string int = 123;
select column1,column2 from table where colString = @string
This should cause an implicit conversion and wouldn't be far fetched when somebody knows they are searching for an integer value in the column.
That will not cause an implicit performance problem (assuming colString really is a string) as you're comparing a string column to a variable defined as varchar.
Oops moving too fast. Meant to make the above change too.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 25, 2014 at 7:23 am
amns (9/25/2014)
Now you should also look at the size difference between the two tables to see if there is much difference.
Sorry, but I didn't understand what you mean. The table is the same, as the results.
Meaning - what is the storage size of the table(s) when you have made those changes? You are storing different data types in a string column
Performance results for several options:
...
select column1,column2 from table where colString = 123
(1 row(s) affected)
This caused in implicit conversion.
logical reads 25599
SQL Server Execution Times:
CPU time = 281 ms, elapsed time = 113 ms.
Regards!
This is the kind of thing you now have to worry about more when storing different data types in the same data type column. People will write code to compare an int to your varchar string. Performance will start to go down.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 25, 2014 at 7:40 am
SQLRNNR (9/25/2014)
This is the kind of thing you now have to worry about more when storing different data types in the same data type column. People will write code to compare an int to your varchar string. Performance will start to go down.
Yep. This is the single biggest concern.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply