Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

common Question about CustomerID search Expand / Collapse
Author
Message
Posted Friday, December 21, 2007 11:40 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 6:03 AM
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)

;)
Post #435793
Posted Friday, December 21, 2007 11:48 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
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
Post #435801
Posted Friday, December 21, 2007 12:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 6:03 AM
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
Post #435840
Posted Friday, December 21, 2007 12:47 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
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
Post #435854
Posted Friday, December 21, 2007 1:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 6:03 AM
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
Post #435879
Posted Friday, December 21, 2007 1:51 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, November 14, 2014 12:37 PM
Points: 2,361, Visits: 6,752
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
Post #435882
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse