By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
462,354 Members | 600 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 462,354 IT Pros & Developers. It's quick & easy.

Which implementation will give faster result (Table design to Joins)

P: 20
I have two tables Users and UserLogin. Here i will use two methods of table design. and which query will return me the result more fast.
The table size will be large that it may contain records in lakhs.

Method 1:
Tables:
Users ( UserID varchar(20) primary key, PassWord varchar(20))
UsersLogin(UserID varchar(20),LoginDate DateTime)
Query:
Select Users.UserID,UsersLogin.LoginDate
from Users U inner join UsersLogin UL
on Users.UserID = UsersLogin.UserID
Method 2:
Tables:
Users ( SysUserID Integer primary key,UserID varchar(20), PassWord varchar(20))
UsersLogin(SysUserID Integer, LoginDate DateTime)
Query:
Select Users.UserID,UsersLogin.LoginDate
from Users U inner join UsersLogin UL
on Users.SysUserID = UsersLogin.SysUserID
Here for the seccond method i made SysUserID additional which will be a system generated ID, and for joins which will give better performance.
Feb 26 '07 #1
Share this Question
Share on Google+
2 Replies

dorinbogdan
Expert 100+
P: 839
The second option could be better, but I'm not sure if would show too much difference. Regulary, the SQL engine is always optimizing the queries before executing.
Feb 26 '07 #2

Motoma
Expert 2.5K+
P: 3,237
You could try taking a look at Query Analyzer's "Estimated Execution Plan" option. Highlight a query, go to the Tools menu, and select "Display Estimated Execution Plan."
Feb 26 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Browse more Microsoft SQL Server Questions on Bytes