Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


common Question about CustomerID search


common Question about CustomerID search

Author
Message
misandrew
misandrew
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 73
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)

Wink
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 6852
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
misandrew
misandrew
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 73
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
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 6852
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
misandrew
misandrew
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 73
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
Roy Ernest
Roy Ernest
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2496 Visits: 6852
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search