•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS SQL section within the Web Development category of DaniWeb, a massive community of 374,169 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 3,344 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS SQL advertiser:
Views: 375 | Replies: 1
 |
•
•
Join Date: Jul 2007
Posts: 12
Reputation: 
Rep Power: 2
Solved Threads: 0
Newbie Poster
common Question about CustomerID search 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)
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
Location: KSA
Posts: 139
Reputation:
Rep Power: 1
Solved Threads: 26
Junior Poster
Hi,
Create a stores procedure (called ex: CustInfo) and forward it the customer code to search for. The body of the procedure will be:
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)
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)
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.
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.
|
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
•
•
•
•
•
•
•
•
DaniWeb MS SQL Marketplace
Linear Mode
•
•
•
•
adsense advertising ajax asp blogging bomb book business coding copyright database deleting records from ms sql table where columns have duplicate values developer development engine environment failure forum google hacker internet legal malware marketing mcafee microsoft monetization msdn news office operating phishing photo privacy publishing revenue search security software spyware sql support survey system technical vista web webmaster wiki yahoo
- Previous Thread: ASP and MSSQL : Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
- Next Thread: SQL DB and WebSite
