Optimization Technique
Jul 23, 2005Sorry for my bad english
What is the optimization technique used by SqlServer dbms?
Thanks
ADVERTISEMENT
Appropriate Query Optimization Technique(s) Sought For The FollowingCase
Nov 25, 2007Hi,Env: MS SQL Server 2000DB Info (sorry no DDL nor sample data):tblA has 147249 rows -- clustered index on pk (one key ofdatatype(int)) andhas two clumns, both are being used in joins;intersecTbl4AB has 207016 rows -- clustered index on two fks andthis intersection table has six colums but only the two fks are beingused for joins;tblB has 117597 rows -- clustered index on pk (one key ofdatatype(bigint)) andhas four columns but only its key are being used for joinsA complex query involving the above the three tables includes innerand outer joins, aggregate, sorting, predicate, math function, derivedtable etc.On the first run, the query takes about 4200ms to finish;after some research on index optimization, I provided some index hint,then the query runs atabout 3000ms. (That was yesterday).Just now I realized that MS SQL Server 2000 is quite "intelligent", Ithink it saves search termsinto cache because the second time search of the a same term is muchmuch faster. Now, a couple of questions:a) if I construct a long long list of "common" terms andprogrammatically let the sql server to cache them would it speed upthe overal query performance in my case? (Or it may depend on thequality of the "common" terms?) and if your answer is yes (supposedlyyou've been there, do you have to know where I could find such a"common" term list, for everyday life or the general public?)b) what other techniques out there to speed up the above describedquery? Bring it down to 1000ms would be most desirable.Thanks.
View 1 Replies View RelatedTable Data Retrieval And Optimization Optimization Help
Apr 10, 2008Hello Everybody,
I have a small tricky problem here...need help of all you experts.
Let me explain in detail. I have three tables
1. Emp Table: Columns-> EMPID and DeptID
2. Dept Table: Columns-> DeptName and DeptID
3. Team table : Columns -> Date, EmpID1, EmpID2, DeptNo.
There is a stored procedure which runs every day, and for "EVERY" deptID that exists in the dept table, selects two employee from emp table and puts them in the team table. Now assuming that there are several thousands of departments in the dept table, the amount of data entered in Team table is tremendous every day.
If I continue to run the stored proc for 1 month, the team table will have lots of rows in it and I have to retain all the records.
The real problem is when I want to retrive data for a employee(empid1 or empid2) from Team table and view the related details like date, deptno and empid1 or empid2 from emp table.
HOw do we optimise the data retrieval and storage for the table Team. I cannot use partitions as I have SQL server 2005 standard edition.
Please help me to optimize the query and data retrieval time from Team table.
Thanks,
Ganesh
PM Technique
Jan 19, 2008Hello Members and Contributors and All the Peoples, Assalamualikum;
I am a Univesity Student and Currently i am creating my own website on ASP.
I have little bit of problem in Personal Message or Private Message Section.
What i want is that my Message Box Field of PM Should render HTML tags.. like here , in this website, Message Box renders HTML tags.. also here it has option to see the HTML view...
ALSO in my SQL Database the maximum length of my PM is 4,000... obviously that field will not store if it increases the limit. So how i increase my PM Field Length. Because i have put the option of replying the message... and old message will be shown as quoted message so all the text is very much important and what technique should use to STOP <a href> tags
SP Technique --
Jul 20, 2005I'm a newbie when it comes to the SQL Server side of things. I've been doingAccess on and off for years.I've got a form that is the main point of entry for all data.As a matter of technique, should I use one stored procedure to validate data(testing for uniqueness mostly) then another one to actually write the datato the tables?Does it make a difference one way or another to the performance?Do I gain any flexibility in my app dong it that way (Access Data Project)?Your help is appreciated.--Jake
View 2 Replies View RelatedWhat's The Best Technique ???
Nov 23, 2007
Hi, we currently use binding to bind data from an SQL Query to a DataGrid, the problem is that we offer the choice to the user to choose the field that he want to see in the grid.
We currently include ALL optional field in the query even if they are not used by the grid, that make a realy big query with MANY inner and left outer join.
Whats the best techinique to dynamicaly construct my query with the field that the user choose.
All suggestion or webpage link will be appreciated.
Thanks a lot.
Paging Technique
Aug 8, 2007Questoin
I am using Sql Server 2000.
I have a table named Cities which has more than 2600000 records.
I have to display the records for a specific city page wise.
I don't want to compromise with performance.
Can anyone has the idea?
Waiting for your fruitful response.
Happy Day And Night For All
Muhammad Zeeshanuddin Khan
Is Using A SP Return Value Bad Technique?
Aug 28, 2004I need to write a query in an SP that returns either yes or no. Is it bad technique to use the return value of the SP for this? Should I use a scalar or output parameter instead? If so which?
Thanks,
Steve
A Question Of SP Technique...
Jul 20, 2005Hi AllI'm creating some SPs and I've got a query which is inserting datainto a table with a a unique constraint:CREATE TABLE [fil_Films] ([fil_ID] [int] IDENTITY (1, 1) NOT NULL ,[fil_Film] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOTNULL ,CONSTRAINT [PK_Tfil_Film] PRIMARY KEY NONCLUSTERED([fil_ID]) ON [PRIMARY] ,CONSTRAINT [IX_fil_Films] UNIQUE NONCLUSTERED([fil_Film]) ON [PRIMARY]) ON [PRIMARY]GOWhen I insert data, should I check in the SP to see if there is anexisting record or simply catch the error if it already exists? Whichis the better technique? My current SP looks like:CREATE PROCEDURE spAddFilm (@Type varchar(50)) ASDECLARE @Count intSET NOCOUNT ONSELECT @Count = Count(fil_ID) FROM fil_Films WHERE fil_Films.fil_Film= @TypeIF @COUNT IS NULLBEGININSERT INTO dbo.Tfil_Film (Tfil_Type) VALUES (@Type)RETURN 1 -- OKENDELSEBEGINRETURN 2 -- ExistsENDGOThanksSam
View 2 Replies View RelatedData Transfer Technique
Jan 15, 2008Been handed a system which inserts millions of records per day into a single table with a composite 'wide' index (they tell me it's the only way to achieve uniqueness) and they run large financial reports from this 300,000,000 row table and of course we are I/O is an understatment.
I wish to split the data feed (inserts) workload away from the reporting tables however I need method to transfer the feed data into the report tables and control the volume of traffic. first choice is replication but is this sufficiently robust enough for a commodeties system? If not any ideas?
Opinions About Insertion Technique
Jul 20, 2005Looking for some insight from the professionals about how they handlerow inserts. Specifically single row inserts through a storedprocedure versus bulk inserts.One argument are people who say all inserts (and updates and deletionsI guess) should go through stored procedures. The reasoning is thatthe developers that code the client side have no reason to understandHOW the data is stored, just that it is. Another problem is an insertthat deals with multiple tables. It would be very easy for thedeveloper to forget a step. That last point also applies to businesslogic. In my case, adding a security to our SecurityMaster can touch 1to 4 tables depending on the type of security. Also, certain fieldsare required while others are set to null for depending on the type.Because a stored procedure cannot be passed datasets but only scalarvalues, when you need to deal with multiple (i.e. bulk) rows you arestuck using cursors. This post is NOT about the pros and cons ofcursors. There are plenty of those on the boards (some of themprobably started by me and showing my understanding (or morecorrectly, lack of) of the way to do things). Stored procedures alsogive you the ability to abort and/or log inserts that cannot happenbecause of contraints and/or business rule failures.Another approach is to write code (not accessible from outside thedatabase) that handles bulk inserts. You would need to write in rulesto "extract" or "exclude" rows that do not match constraints orbusiness rules otherwise ALL the inserts would fail because of one badrow. I guess you could put the "potential" rows into a temp table.Apply your rules to the temp table and delete / move rows that wouldfail. Any rows left can that be bulk inserted. (You could also use therows that were moved to another temp table for logging why theyfailed.)So that leaves use with two possible ways to get data into the system.A single row based approach for client apps and a bulk based forinternal use. But that leaves use with another problem. You now havebusiness logic in TWO separate areas. You have to remember to modifycode or fix bugs in multiple locations.For those that are still reading my post, my question is...How do you handle this? What is the approach you take?
View 4 Replies View RelatedTechnique For Concurrent Access?
Jul 20, 2005I've been asked to turn our single-user database system into a multi-usersystem. At present, each user has a copy of the MSDE on their desktopmachine and uses our program to access it. In future, we would like tocentralise our MSDE instance and allow multiple users to access it. Inorder to facilitate this, we are going to only allow one user write accessto the system at a time (I know, its a kludge, but the system was neverdesigned for multiple users in the first place).I have a single, simple question this being the case: can I update a single"read-only" bit field in a table of the database in order to flag to otherusers that the system is in read-only mode in a way that avoids concurrencyissues? ie. does an "UPDATE" query lock and unlock? ( I suspect the answeris yes! ). If anyone else has experience of these things, I would alsoappreciate some tips on how best to proceed.ThanksRobin
View 7 Replies View RelatedPowerful Search Technique..
Apr 14, 2007hello.
How to do Search in the method which is done in MSDN library 2005 (local application)?
i.e enabling a search filter and then (most incredibly) as you type the criteria, the
list gets updated depending on the characters entered.
for eg. you type: loo
and obtain the following display:
Look in dialog box
Look tab
lookaheads
l
l
l
l
l
l
l
l
l
l
l
l
l
How to use this fast searching technique in vb.net 2005 and the items to be searched in
sql server 2005 express??
Feedback Requested: SCD Technique
Apr 11, 2008Hi folks, I have implemented this technique to simplify SCD loads and also to maintain consistent units of work during update/insert of a single row. Wanted to get your feedback on this technique: performance, transaction issues, etc.
I send all rows to an OLE DB Command that performs both update and insert for a single row in a single command:
Code Snippet
UPDATE PROPERTY SET ORD_TERM_DT = ? WHERE ACCOUNT_NBR = ? AND ORD_TERM_DT = '9999-12-31 23:59:59';
INSERT INTO PROPERTY (
ACCOUNT_NBR
, APPRAISAL_COMPANY_CD
, .....
, ORD_TERM_DT
) VALUES (?, ...,?);
This way I can guarantee that if the termination (update) of an old row (say, row 10) succeeds, but insert of the new row 10 fails, that it will roll back. Otherwise, row 10 will get terminated without being replaced with a current record...
Performance: load of 7,734 changed records into a table of 6.8M existing records was roughly 8 seconds. The data flow task container TransactionOption = Required.
Discovery Technique For Last Execution Stamp
Apr 8, 2008I'm looking to remove hundereds of legacy procs, triggers, functions etc in a DB. Is there a DMV, sys.<something> command or technique that will tell me the last night an object was accessed or executed?
View 2 Replies View RelatedPinning Technique's In Sqlserver 2005
May 22, 2008
I have Came to know that Pinning concept is removed in sqlserver 2005 .
So is there any alternate for that .Any links regardint that which provides information.
Can't Select Data Mining Technique....
Nov 14, 2006Colleagues:
I'm working through the DM tutorials and I can't select a data mining technique when creating a new mining structure in BI Studio - BI Studio locks up totally. Have installed SQL Svr 20005 SP1 and components from feature pack. Analysis Server service is running, have set the properties in the DM project properties to the running instance of Analysis Services. Have read the various posts on the forum and have double checked my configuration - all seems as it should be.
All suggestions appreciated - very much looking forward to working with the technology.
Michael (michael.dataSense@sympatico.ca)
My First Post: Smart SQL Updating Technique..??
Jul 12, 2007Hello geniuses
First of all I would like to announce that this is my first time I post here.. However, I'm pretty sure that I'm in the best place to ask what I want. To cut the story short, I'm querying SQL database on a remote machine and having the result saved (mapped) to another table on another database on the same remote machine. The thing is the destination table was empty before the query was run the first time. I have been searching for some smart way so that when I modify the source tables that my query is based on, it doesn't affect except the modified rows. In other words, it should be like if the row is already there, do nothing. otherwise, it updates the existig record. else, it's a new record and it's inserted. I think what i need will include some coding for sure, yes? I don't know if i'm clear about the requirement or not though! but I know that you are experts and can direct me. Waiting for your valuable replies.
Sherif Magdi
Ideal Event Handling Technique
Apr 13, 2007Hi All,
We have this SSIS package that serves a ETL role, but sometimes when the package crashes we wanted our support personnel to be aware of it, so we did implement an Event handler Package, executable is the package and Event Handler on taskFailed. The idea is if any of the Tasks fail, then trigger the event handler.
Now recently I found out while debugging the package, one of the DFT's failed, and the control went to the Event handler. But thenrest of the Task's are executing and the package finished successfully.
How do I ensure that if at all some Tasks fail, the control should invoke the task at the event handler and end the package run.
One more query, what is the ideal and best way of Event handling to be followed in case of ETL jobs ?
Thanks in Advance.
There Is Any Service Or Technique To Reflect Database Changes To Form
May 20, 2007there is any service or technique to reflect database changes to form?
am mean if there is two people update same data and one of them
does the update i need (search) on service in sql server or .net that(or triger) that can automatically reflect changes to the form control that display data automatically when data changes before the another persone make second aupdate, so he can see the update made by the first person pefore he make another update.
Thanks So Much for who try to answer.
A Relational Technique To Strip The HTML Tags Out Of A Ntext Datatype Field
Nov 27, 2007I had a problem with the ntext datatype. I need to strip the HTML tags out of a ntext datatype column. I have sample query for that, which works fine for STRING, as stuff is the string function, what to do for ntext field.
=======The Process follows like this =========
--**************************************
--
-- Name: A relational technique to strip
-- the HTML tags out of a string
-- Description:A relational technique to
-- strip the HTML tags out of a string. Th
-- is solution demonstrates how to use simp
-- le tables & search functions effectively
-- in SQL Server to solve procedural / ite
-- rative problems.
-- This table contains the tags to be re
-- placed. The % in <head%>
-- will take care of any extra informati
-- on in the tag that you needn't worry
-- about as a whole. In any case, this t
-- able contains all the tags that needs
-- to be search & replaced.
CREATE TABLE #html ( tag varchar(30) )
INSERT #html VALUES ( '<html>' )
INSERT #html VALUES ( '<head%>' )
INSERT #html VALUES ( '<title%>' )
INSERT #html VALUES ( '<link%>' )
INSERT #html VALUES ( '</title>' )
INSERT #html VALUES ( '</head>' )
INSERT #html VALUES ( '<body%>' )
INSERT #html VALUES ( '</html>' )
go
-- A simple table with the HTML strings
CREATE TABLE #t ( id tinyint IDENTITY , string varchar(255) )
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Some Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css" TYPE="text/css" ></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">
SOME HTML text after the body</HTML>'
)
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Another Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css"></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">Another HTML text after the body</HTML>'
)
go
-- This is the code to strip the tags out.
-- It finds the starting location of eac
-- h tag in the HTML string ,
-- finds the length of the tag with the
-- extra properties if any. This is
-- done by locating the end of the tag n
-- amely '>'. The same is done
-- in a loop till all tags are replaced.
BEGIN TRAN
WHILE exists(select * FROM #t JOIN #html on patindex('%' + tag + '%' , string ) > 0 )
UPDATE #t
SET string = stuff( string , patindex('%' + tag + '%' , string ) ,
charindex( '>' , string , patindex('%' + tag + '%' , string ) )
- patindex('%' + tag + '%' , string ) + 1 , '' )
FROM #t JOIN #html
ON patindex('%' + tag + '%' , string ) > 0
SELECT * FROM #t
rollback
What Technique To Use To Update Production Table From Staging Table
Mar 9, 2007Hi I have a production table in SQL Server 2005 that has approx 500,000 records---every 6 hours this table needs to be truncated and filled
The basic SSIS package uses a script compentant and imports the data into a staging table which has the same structure as the production table. I have a final Execute SQL Task that Truncates the production table and does a Insert into production-select * from stage table.
Takes around 30 seconds to run this last Execute SQL Task--problem is there is a risk that our webservice will query this table during the Execute SQL Task and return incorrect results.
Q1: In this last Execute SQL Task if I used a BEGIN TRANSACTION and COMMIT TRANSACTION; will this be any quicker ?
Q2: In this last Execute SQL Task- would it be better to use a RENAME TABLE technique in TSQL--any code examples ??
Q3:Is there any way in TSQL I can compare EVERY FIELD in two tables ie Stage and Production which have identical data structures and figure out a way to update only the records that changed? Is SQL Server Replication the best way to do this
thanks kindly
Dave
Need Optimization
Jun 6, 2007Dear Advance,I used one stored procedure to retrive 3 different result set. and in the codebehind i seperate it. means from the dataset i seperate three different datatable and then show my data as my need.but the main problem is ... after retriving the datafrom the database i have to user foreach loop to bind the coulmns data to my different custom class.example: foreach (DataRow oDrow in MyDataTable.Rows) {oClass=new Class();oClass.Name1=oDrow["Name1"] .toString();oClass.Name2=oDrow["Name2"] .toString();.... } 1. so my first question is there any optimization possible ?2. my result set is too loong ... so should keep just one hit to database or hit more than one time Currently i am optimizing my web application. in the previous version 1 have to hit the database 3/4 times for different purposes. but now it hits only one time... but it takes time in the codebehind to perform different operation.Any Suggestion
View 1 Replies View RelatedOptimization Of An SP
Dec 11, 2001I have a SP that calls about 10 stored procedures sequentially. The 10 SP's are basically complex update statements, each one individual. Is there any way to optimize this?
I know putting the 10 into 1 SP would make it compile faster but thats about it. Are there any execution tricks of Stored Procedures firing off sequentially?..or anything I should know?
-thanks
Bartman
Optimization
Mar 2, 2001Hello All,
What is the best way to optimize this code or rewrite it using ISNULL ?
CREATE PROCEDURE get_employees (@dept char(8), @class char(5))
AS
IF (@dept IS NULL AND @class IS NOT NULL)
SELECT * FROM employee WHERE employee.dept IS NULL
AND employee.class=@class
ELSE IF (@dept IS NULL AND @class IS NULL)
SELECT * FROM employee WHERE employee.dept IS NULL
AND employee.class IS NULL
ELSE IF (@dept IS NOT NULL AND @class IS NULL)
SELECT * FROM employee WHERE employee.dept=@dept
AND employee.class IS NULL
ELSE
SELECT * FROM employee WHERE employee.dept=@dept
AND employee.class=@class
Thanks in advance.
-Praveena
Optimization
Jul 6, 2001I am wondering if the size of the data file makes a difference in running Insert's and/or doing Fetch's. Our DB was 11GB in size, I ran a dbcc shrinkdatabase and it shrank it to 5.5 GB in size, now that it is smaller will it run a select query faster as opposed to when we run large inserts and it has to automatically grow to accommodate the insert. I am trying to figure out if I should leave my .mdf file large or keep it small or does it even make a difference. I am only doing large inserts while loading data to get ready for production after that the inserts will be hourly but much smaller, however our queries to the DB after it is in production will be much more intensive.
Hope this makes sense. Appreciate your help.
Optimization
Jan 9, 2006Hi All,
I am writing a script that runs the optimization process. I am not sure how to test it? Any help is greatly appreciated.
SQL Optimization
Dec 20, 2006Hello,
Is there a tool to optimize SQL procedures code?
Thanks,
Miguel
Optimization
Jul 20, 2005We're building a company wide network monitoring systemin Java, and need some advice on the database design andtuning.The application will need to concurrently INSERT,DELETE, and SELECT from our EVENT table as efficiently aspossible. We plan to implement an INSERT thread, a DELETEthread, and a SELECT thread within our Java program.The EVENT table will have several hundred million recordsin it at any given time. We will prune, using DELETE, aboutevery five seconds to keep the active record set down toa user controlled size. And one of the three queries willbe executed about every twenty seconds. Finally, we'llINSERT as fast as we can in the INSERT thread.Being new to MSSQL, we need advice on1) Server Tuning - Memory allocations, etc.2) Table Tuning - Field types3) Index Tuning - Are the indexes right4) Query Tuning - Hints, etc.5) Process Tuning - Better ways to INSERT and DELETE, etc.Thanks, in advance, for any suggestions you can make :-)The table is// CREATE TABLE EVENT (// ID INT PRIMARY KEY NOT NULL,// IPSOURCE INT NOT NULL,// IPDEST INT NOT NULL,// UNIXTIME BIGINT NOT NULL,// TYPE TINYINT NOT NULL,// DEVICEID SMALLINT NOT NULL,// PROTOCOL TINYINT NOT NULL// )//// CREATE INDEX INDEX_SRC_DEST_TYPE// ON EVENT (// IPSOURCE,IPDEST,TYPE// )The SELECTS areprivate static String QueryString1 ="SELECT ID,IPSOURCE,IPDEST,TYPE "+"FROM EVENT "+"WHERE ID >= ? "+" AND ID <= ?";private static String QueryString2 ="SELECT COUNT(*),IPSOURCE "+"FROM EVENT "+"GROUP BY IPSOURCE "+"ORDER BY 1 DESC";private static String QueryString3 ="SELECT COUNT(*),IPDEST "+"FROM EVENT "+"WHERE IPSOURCE = ? "+" AND TYPE = ? "+"GROUP BY IPDEST "+"ORDER BY 1 DESC";The DELETE isprivate static String DeleteIDString ="DELETE FROM EVENT "+"WHERE ID < ?";
View 2 Replies View RelatedAbout Optimization?
Jul 17, 2007There are two main tables in my app,in order to optimize search via scope condition, I set many indexs for these two tables
however,at the same time the two tables are also used for my etl app,everyday there are more than thousands of data need to be updated or inserted, but index is not suitable for huge modification,any idea about how to handle this?
thanks in advance
Optimization
Oct 15, 2007
Hello,
what is the meaning about <MissingIndexGroup Impact="99.9521"> in the Queryplan? Should I create a Grouped Index? An what is the meaning about Impact="99.9521"?
If the Impact =100 you get a 100% better performance, and if the impact =20 ypu get a 20% better performance, is this the meaning?
finchen
Query Optimization - Please Help
Aug 15, 2007Hi,
Can anyone help me optimize the SELECT statement in the 3rd step? I am actually writing a monthly report. So for each employee (500 employees) in a row, his attendance totals for all days in a month are displayed. The problem is that in the 3rd step, there are actually 31 SELECT statements which are assigned to 31 variables. After I assign these variable, I insert them in a Table (4th step) and display it. The troublesome part is the 3rd step. As there are 500 employees, then 500x31 times the variables are assigned and inserted in the table. This is taking more than 4 minutes which I know is not required :). Can anyone help me optimize the SELECT statements I have in the 3rd step or give a better suggestion.
DECLARE @EmpID, @DateFrom, @Total1 .... // Declaring different variables
SELECT @DateFrom = // Set to start of any month e.g. 2007-06-01 ...... 1st
Loop (condition -- Get all employees, working fine)
BEGIN
SELECT @EmpID = // Get EmployeeID ...... 2nd
SELECT @Total1 = SUM (Abences) ...... 3rd
FROM Attendance
WHERE employee_id_fk = @EmpID (from 2nd step)
AND Date_Absent = DATEADD ("day", 0, Convert (varchar, @DateFrom)) (from 1st step)
SELECT @Total2 ........................... same as above
SELECT @Total3 ........................... same as above
INSERT IN @TABLE (@EmpID, @Total1, ...... @Total31) ...... 4th
Iterate (condition) to next employee ...... 5th
END
It's only the loop which consumes the 4 minutes. If I can somehow optimize this part, I will be most satisfied. Thanks for anyone helping me....
SQL Connection Optimization
Jun 23, 2004Hi guys,
Could any one tell me what is the best way to declare a connection from ASP .net to a SQL database so the sql could support the maximum users, because it seems that the way i'm using is not correct cuz when i make some transactions from my website to the database, the database send an error message saying that there are no more free connections.
Thnx