Member Avatar for 3xxx

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)

;)

  • 2 Contributors
  • 1 Reply
  • 187 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by kb.net

Dani AI

Generated

Short, practical follow-up for (and commentary on 's suggestion).

Splitting “recent” vs “older” customers is a valid approach, but the real win comes from how SQL looks for the row. The stored-proc idea is fine in principle; avoid scanning the whole small table with COUNT(*) and avoid SELECT * (both slow or recover unnecessary IO). Prefer a presence check that stops at the first match, then return only the required columns.

CREATE PROCEDURE GetCustomer(@CustID INT) AS
SET NOCOUNT ON;

-- fast existence check; stops at first matching row
IF EXISTS (SELECT 1 FROM TBLcustomer1 WHERE CustomerID = @CustID)
BEGIN
  -- list needed columns explicitly; do NOT use SELECT *
  SELECT CustomerID, FirstName, LastName, Email
  FROM TBLcustomer1
  WHERE CustomerID = @CustID;
  RETURN;
END

SELECT CustomerID, FirstName, LastName, Email
FROM TBLcustomer2
WHERE CustomerID = @CustID;
GO

Longer‑term options (pick one or combine): make sure CustomerID is indexed (clustered or a nonclustered seekable index); create a covering index that includes the commonly returned columns so SQL Server can answer the query from the index alone; consider consolidating into a partitioned table or a partitioned view so the optimizer can prune partitions instead of probing two separate tables. If duplicates across the two tables are possible, choose and enforce a clear precedence (small table first) with a unique constraint or application rule.

Quick troubleshooting checklist: check the actual execution plan and SET STATISTICS IO/TIME to see where IO is spent; update statistics and rebuild fragmented indexes; test response with real keys. These steps will usually produce the “instant” improvement your boss notices, without application changes.

Member Avatar for kb.net

Hi,

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

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)

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.