MS SQL

common Question about CustomerID search

Please support our MS SQL advertiser: Intel Parallel Studio Home

•

Join Date: Jul 2007

Posts: 18

Reputation: 3xxx is an unknown quantity at this point

Solved Threads: 0

3xxx

Offline

Newbie Poster

common Question about CustomerID search

Dec 21st, 2007

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 first search

select * from TBLcustomer1 -- small talbe

(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 (this depends on searching on small 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)

•

Join Date: Aug 2007

Posts: 160

Reputation: kb.net is an unknown quantity at this point

Solved Threads: 26

kb.net

Offline

Junior Poster

Re: common Question about CustomerID search

Dec 23rd, 2007

Hi,

Create a stores procedure (called ex: CustInfo) and forward it the customer code to search for. The body of the procedure will be:

 Help with Code Tags 
 MS SQL Syntax (Toggle Plain Text) 
CREATE PROCEDURE CustInfo(@CustCod int) AS
 
	IF (SELECT count(*) FROM CustNew WHERE CustNum= @CustCod)>=1 
 
	SELECT * FROM CustNew  WHERE CustNum= @CustCod
 
	ELSE SELECT * FROM CustOld  WHERE CustNum= @CustCod
 
GO
CREATE PROCEDURE CustInfo(@CustCod int) AS

	IF (Select count(*) from CustNew where CustNum= @CustCod)>=1 

	Select * from CustNew  where CustNum= @CustCod

	ELSE Select * from CustOld  where CustNum= @CustCod

GO

Then all what you will need to call is the stored procedure with the parameter which varies depending on the programming language you will use (but you can test it in SQL Query analyzer as: CustInfo 236)

It is never about the number of languages you know, you either have the logic of programming or you don't ...

Some of the codes I post are collected from different sites during the past couple of years, so I would like to thank them for their help and for enabling me to help.