|
|
|
Forum Guru
 
Group: Forum Members
Last Login: 7/30/2008 8:27:47 AM
Posts: 60,
Visits: 89
|
|
Hi,
I would like to know if there would be any performance impact when we join based on Varchar columns
and when we join based on Integer columns.
Case 1: Table1 and Table 2 has EmployeeId as varchar(15)
Select Table1.A,Table2.B.... from Table1 , Table 2 Where Table1.Employee = Table2.Employee
Case 2: Table1 and Table 2 has EmployeeId as Integer
Select Table1.A,Table2.B.... from Table1 , Table 2 Where Table1.Employee = Table2.Employee
In my case the varchar column size is only 15.
I'm not able to load huge volumes of data into these two tables.
With the sample data that i could load, im not able to see any performance difference between the two.
I would like to know if there will be a performance difference.
If someone can give comments on the above scenario it will be really useful.
(Pointers to any thoeretical explanation in any documens would also be very helpful)
Regards,
Annal T
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 2 days ago @ 4:46:44 AM
Posts: 134,
Visits: 370
|
|
Hello,
It depends on numerous factors such as data, data volume, indexes defined on both tables and type of join used. And logically speaking yes it will affect the performance. And the worst scenario will be joining on the SUBSTR( ).
HTH.
Regards,
Adeel
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Yesterday @ 8:42:44 AM
Posts: 41,
Visits: 93
|
|
Yes joins on integer and varchar affects the performance.
As varchar is continuously vary in character lenght and integer doe not.
And for any DB it is always easy to operate on fixed lenght object as compare to varying one.
If you will use char on the place of varchar then u will see that there the difference in performane of char and integer will decrease.
Regards,
Subhash
Regards,
Subhash
|
|
|
|
|
Forum Guru
Group: Forum Members
Last Login: 7/30/2008 8:27:47 AM
Posts: 60,
Visits: 89
|
|
Hi All,
Thanks for the inputs!!!
I'm not considering scenarios where i would be using substr or any concatenation etc.
(Other factors like Data volume, any kind of indexes created can also be considered the same for both cases)
From the inputs i could see that the performance impact is attributed to character column being of variable length.
Subash,
From the point i guess this performance impact would be seen even in queries without join when we deal with Varchar columns.
Can you please let me know how variable length column is affecting query performance?
(Is it becos it has to read the VLI and fetch data? Is this a significant overhead when we deal with huge data volume?)
Regards,
Annal T
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 9/4/2008 1:20:38 PM
Posts: 215,
Visits: 395
|
|
|
IMHO, the cost difference between varchar and integer in comparing the column values would be miniscule. The cost of the join will be overwhelmingly dominated by other factors.
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: Yesterday @ 8:42:44 AM
Posts: 41,
Visits: 93
|
|
Hi,
Do u mean VLI for variable length input.....
If yes then it's true....
while dealing with varchar system has to vary length of storage continuously.....
And if we deal with large volume of data we can see the overhead with such data.
And if u want to see to this in practical way then look at multi value compression....
Due to the same reason MVC doesn't allow compression on varchar....
Regards,
Subhash
Regards,
Subhash
|
|
|
|