InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 22:43:35 GMT20http://www.sqlservercentral.com/Forums/Topic435793-360-1.aspxIndexing 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 exampleSelect Col1, Col2, col3 from tbCustomerTbl1 with (readuncommitted)where CustomerID = @parameterThis 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)Fri, 21 Dec 2007 13:51:32 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic435793-360-1.aspxmany thanks for the detail answer--- one last Q --how about , clustering and (or network load balancing in the windows 2003 server itself)andadding one more server computer, and using Master/ Target features of SQL server 2005for the above methodswill 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 guidanceFri, 21 Dec 2007 13:43:21 GMTmisandrewhttp://www.sqlservercentral.com/Forums/Topic435793-360-1.aspxThe SQL Query would be like this...Select CustomerID, col2, col3 from tbCustomerTbl1where CustomerID = @parameterIf (@rowcount = 0)Select CustomerID, col2, col3 from tbCustomerTbl2where CustomerID = @parameterThat 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.Fri, 21 Dec 2007 12:47:55 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic435793-360-1.aspxI think , I want both - could you give us some SAMPLEs -- full set of sample code please(thanks for replying)for store procedurefor raw SQL (as I saw some company using raw SQL in the ASP , but when manager retrieving report, it slows downwhen some users, routinely enter new data (some simple data, like payment retrieval, orwhen 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 thenooooops, I install as the SAME INSTANCE in SQL server 2000, then the system CRASHand 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 v7MANY THANKS for guidanceFri, 21 Dec 2007 12:34:16 GMTmisandrewhttp://www.sqlservercentral.com/Forums/Topic435793-360-1.aspxAre 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.Fri, 21 Dec 2007 11:48:46 GMTRoy Ernesthttp://www.sqlservercentral.com/Forums/Topic435793-360-1.aspxprobably, all of you have seen this scenario a lotwhen entering customerID into a large databaseit takes AGES to bring back one simple record of a single customer===let's say, this has been DONE already , for tuning performanceseparate the CUSTOMERS table into 2TBLcustomer1 -- 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 etcQ:how to write a sql stmt (i.e. in SQL server level, rather than programming level)when USER ENTER one customerID for searching it willselect * 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 TABLEthen 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);)Fri, 21 Dec 2007 11:40:28 GMTmisandrew