December 21, 2007 at 11:40 am
probably, all of you have seen this scenario a lot
when entering customerID into a large database
it takes AGES to bring back one simple record of a single customer
===
let's say, this has been DONE already , for tuning performance
separate the CUSTOMERS table into 2
TBLcustomer1 -- all the customers that got access in the last 2 months (small tables)
TBLcustomer2 -- all the customers in the company (that exclude TBL customer 1)
===== >> obj: without modifying the FORM code or VB/ASP(.net) code etc
Q:
how to write a sql stmt (i.e. in SQL server level, rather than programming level)
when USER ENTER one customerID for searching
it will
select * from TBLcustomer1
(small table first, if THE CUSTOMER IS FOUND, then exit IF/then for..loop etc, and then all the customers details will be supplied to the program)
IF THE CUSTOMER IS NOT FOUND in the SMALL TABLE
then the program WILL search the much bigger table (it will take much longer to search this BIG table)
select * from TBLcustomer2 -- this will take a long time
===== GURU: could you suggest some ways to do it ===
this is very common, I have seen such scenario in ORACLE and SQL server, please kindly propose some viable solution -- I think this could be a Boss-Pleaser (to see instant improve in performance)
😉
December 21, 2007 at 11:48 am
Are you calling a stored Proc to query the DB from your form? Or is it Raw SQL?
If Stored Proc you can do it quite easily with an IF Statement.
Either you can use Date as the criterea for determining which table to look at or you can use @@rowcount.
Select from small table, Check @@rowcount. If that is 0, then look at the big table.
-Roy
December 21, 2007 at 12:34 pm
I think , I want both - could you give us some SAMPLEs -- full set of sample code please
(thanks for replying)
for store procedure
for raw SQL (as I saw some company using raw SQL in the ASP ,
but
when manager retrieving report, it slows down
when some users, routinely enter new data (some simple data, like payment retrieval, or
when lots of customer service dept users, check on data) all these sceneraio will SLOW down the system -- till to a point that it have to restart the computer, every 2 days
( I believe, they use Raw SQL , that partially account for such SLOWness)
--- i.e. , could you give us some sample on both (simple but full set of coding)
do you have some other idea (besides index tuning, that could make the system faster)
I did try to upgrades it SQL server v7 to SQL server 2000, but then
ooooops, I install as the SAME INSTANCE in SQL server 2000, then the system CRASH
and because of some strange reason , SQL server 2000, can't be uninstalled -- got some error)
-- beside the full code in both RAW / SP, could you suggest some other Performance Tuning too
-- the set up is one Dual CPU computer, using windows 2003 standard edition, running BOTH the ASP program and running the SQL server v7
MANY THANKS for guidance
December 21, 2007 at 12:47 pm
The SQL Query would be like this...
Select CustomerID, col2, col3 from tbCustomerTbl1
where CustomerID = @parameter
If (@rowcount = 0)
Select CustomerID, col2, col3 from tbCustomerTbl2
where CustomerID = @parameter
That should retrieve the rows. Make sure you have an Index on CustomerID.
Performance increase is not an easy thing to explain. You can increase performance using a better Hardware, Query tuning and lots of small things which adds upto a big chunk of permonce increase.
-Roy
December 21, 2007 at 1:43 pm
many thanks for the detail answer
--- one last Q --
how about , clustering and (or network load balancing in the windows 2003 server itself)
and
adding one more server computer, and using Master/ Target features of SQL server 2005
for the above methods
will there be any thread in SQLserverCentral.com that would tell us- about the precaution (or procedures) during such upgrade
=== for the CRASH that I mentioned earlier, is it the mistake that I try to upgrade with the same instance name -- during new installation===
thanks for all your guidance
December 21, 2007 at 1:51 pm
Indexing is something that you have to do carefuly. Study the Data and first try to find out what is the main criterea in almost all the queries. If it is based on CustomerID , I would say add a Clustered Index on CustomerID. That should speed up the query a bit.
You could try replicating the DB and run all Select queries on the replicated DB and the updates and insert on the Main DB.
If Data Integrity is not so important you can write queries with hints.
For example
Select Col1, Col2, col3 from tbCustomerTbl1 with (readuncommitted)
where CustomerID = @parameter
This will be a bit more faster than without Hint because in this case, it does not have to aquire any kind of lock. But you might select data that has not been committed yet. (maybe this non committed Data might get rolled back)
-Roy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply