Which DELETE Will Be Faster?

(Update Or Delete) Which Is Faster.
Does anyone know if it is faster to update a record with a delete flag or to delete the record.

-Bill

View Replies !   View Related

---

What Is Faster?! Update Or Delete And Insert
Hello,

What is faster / better for performance?

Check if something already excist and update the changed values? or delete the whole table and then insert everything?

Thanks!

View Replies !   View Related

---

Which Would Be Faster ?
hi all,    if i have a comma delimited string and want to insert each delimited substring into a table which of the following way is faster?pass the whole string into the a stored procedure and loop through the delimited string and pick out the substring and insert into the table orloop and pass the substring into a stored procedure and insert N times?or any other better ways someone could suggest me to do thanks! 

View Replies !   View Related

---

NEED TO GO FASTER


View Replies !   View Related

---

Which Is Faster?
I'm sonewhat new to MS SQL Server and I'm wondering about which of thefollowing two queries would be faster:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSELECT@ResidencesBuilt = SUM(CASE WHEN BuildingType = 0 THEN Built END),@BarracksBuilt = SUM(CASE WHEN BuildingType = 1 THEN Built END),@AirBaysBuilt = SUM(CASE WHEN BuildingType = 2 THEN Built END),@NuclearPlantsBuilt = SUM(CASE WHEN BuildingType = 3 THEN Built END),@FusionPlantsBuilt = SUM(CASE WHEN BuildingType = 4 THEN Built END),@StarMinesBuilt = SUM(CASE WHEN BuildingType = 5 THEN Built END),@TrainingCampsBuilt = SUM(CASE WHEN BuildingType = 6 THEN Built END),@FactoriesBuilt = SUM(CASE WHEN BuildingType = 7 THEN Built END)FROM BuildingsGROUP BY kdIDHAVING kdID = 2902Or:DECLARE @ResidencesBuilt intDECLARE @BarracksBuilt intDECLARE @AirBaysBuilt intDECLARE @NuclearPlantsBuilt intDECLARE @FusionPlantsBuilt intDECLARE @StarMinesBuilt intDECLARE @TrainingCampsBuilt intDECLARE @FactoriesBuilt intSET @ResidencesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 0AND kdID = 2902)SET @BarracksBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 1 ANDkdID = 2902)SET @AirBaysBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 2 ANDkdID = 2902)SET @NuclearPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =3 AND kdID = 2902)SET @FusionPlantsBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 4AND kdID = 2902)SET @StarMinesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 5AND kdID = 2902)SET @TrainingCampsBuilt = (SELECT Built FROM Buildings WHERE BuildingType =6 AND kdID = 2902)SET @FactoriesBuilt = (SELECT Built FROM Buildings WHERE BuildingType = 7AND kdID = 2902)The data source is:kdID BuildingType Built2902 6 02902 7 02902 4 02902 0 802902 2 02902 1 52902 3 402902 5 10Or:CREATE TABLE [dbo].[Buildings] ([kdID] [int],[BuildingType] [tinyint],[Built] [int])INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 0, 80)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 1, 5)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 2, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 3, 40)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 4, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 5, 10)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 6, 0)INSERT INTO Buildings (kdID, BuildingType, Built) VALUES (2902, 7, 0)Analyzer says the first would be faster, but it has a lot of SUM()'s andwhatnot so I'm not too sure about this. There are also about 1000 rows inthe actual Buildings table. This will be a part of a stored procedure.

View Replies !   View Related

---

Can This Be Done Faster?
I was just wondering if this can be done any faster? code-wise that is...

Don't mind the converts, can't do without them, as the data discipline for the source table isn't always reliable, while I have to be absolutely sure the destination data ends in the required format.



UPDATE MATCH_basistabel
SET MATCH_basistabel.matchfelt = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.søgenavn = convert(varchar(50),ALL_tbl_medlemsinfo.søgenavn),
MATCH_basistabel.medlemsnavn = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n),
MATCH_basistabel.medlemsnavn2 = convert(varchar(50),ALL_tbl_medlemsinfo.medlemsnav n2),
MATCH_basistabel.medlemsnummer = ALL_tbl_medlemsinfo.medlemsnummer,
MATCH_basistabel.nationalitet = convert(varchar(10), ALL_tbl_medlemsinfo.nationalitet),
MATCH_basistabel.organisationsnummer = convert(varchar(10),ALL_tbl_medlemsinfo.organisati onsnummer),
MATCH_basistabel.medlemskab = convert(varchar(20), ALL_tbl_medlemsinfo.medlemskab),
MATCH_basistabel.ipdn = ALL_tbl_medlemsinfo.ipdn,
MATCH_basistabel.ipdnroll = convert(varchar(20), ALL_tbl_medlemsinfo.ipdroll),
MATCH_basistabel.franavision = 1
FROM MATCH_basistabel, ALL_tbl_medlemsinfo
WHERE isnumeric(matchfelt) = 1
AND (convert(int, MATCH_basistabel.matchfelt) = convert(int, ALL_tbl_medlemsinfo.medlemsnummer)
AND MATCH_basistabel.franavision = 0)

View Replies !   View Related

---

Run Faster
Hi Guys,

I have SQL file but it run slowly when comes to huge record. How do I make it faster. I do create an index but how to make use the index? Pls help me on this...

Many Thanks,

Regards,
Shaffiq

View Replies !   View Related

---

Which Is Faster LIKE Or IN
i have a query that i can use either and get the same results. i just need to shave some time off... which is faster the LIKE or IN () ???

thanks

kim

View Replies !   View Related

---

Faster Way To Do This?
I want to know the # of users on our web site for each month in a given year.  I'm looking for a faster way to do this--perhaps one that can leverage an index instead of reading the entire table!  (My avg disk queue right now is above 7 and the query takes about 90 seconds).

 

Here's my current SP.  Basically I'm calculating each month/year and using UNION to join them together, then pivot to rotate.

 

USE [TNS]

GO

/****** Object: StoredProcedure [dbo].[Unique_Login_IPs] Script Date: 05/07/2007 12:38:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTER PROCEDURE [dbo].[Unique_Login_IPs]

(

@year1 int,

@year2 int

)

AS

BEGIN

SET NOCOUNT OFF;

 

-- Define the years for testing purposes

set @year1 = 2006

set @year2 = 2007

 

SELECT month,[2006] as y2006,[2007] as y2007

FROM

(

SELECT @year1 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @year1 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 2)) as tmpy1_2

UNION

SELECT @year1 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 3)) as tmpy1_3

UNION

SELECT @year1 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 4)) as tmpy1_4

UNION

SELECT @year1 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 5)) as tmpy1_5

UNION

SELECT @year1 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 6)) as tmpy1_6

UNION

SELECT @year1 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 7)) as tmpy1_7

UNION

SELECT @year1 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 8)) as tmpy1_8

UNION

SELECT @year1 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 9)) as tmpy1_9

UNION

SELECT @year1 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 10)) as tmpy1_10

UNION

SELECT @year1 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 11)) as tmpy1_11

UNION

SELECT @year1 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year1) AND (MONTH(logged) = 12)) as tmpy1_12

UNION

SELECT @year2 AS year, 1 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 1)) as tmpy1_1

UNION

SELECT @year2 AS year, 2 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 2)) as tmpy2_2

UNION

SELECT @year2 AS year, 3 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 3)) as tmpy2_3

UNION

SELECT @year2 AS year, 4 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 4)) as tmpy2_4

UNION

SELECT @year2 AS year, 5 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 5)) as tmpy2_5

UNION

SELECT @year2 AS year, 6 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 6)) as tmpy2_6

UNION

SELECT @year2 AS year, 7 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 7)) as tmpy2_7

UNION

SELECT @year2 AS year, 8 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 8)) as tmpy2_8

UNION

SELECT @year2 AS year, 9 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 9)) as tmpy2_9

UNION

SELECT @year2 AS year, 10 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 10)) as tmpy2_10

UNION

SELECT @year2 AS year, 11 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 11)) as tmpy2_11

UNION

SELECT @year2 AS year, 12 AS month, COUNT(*) AS cnt

FROM (SELECT DISTINCT ipaddress

FROM servicelog AS servicelog_1

WHERE (method = 'LOGIN') AND (YEAR(logged) = @year2) AND (MONTH(logged) = 12)) as tmpy2_12

) piv

PIVOT

(

SUM(cnt)

FOR year IN

([2006],[2007])

) as child

END

View Replies !   View Related

---

Faster Than Cursor?
I have a cursor prcedure that is pretty slow because as the cursor moves through the data I have three select statement on the same table to find other rows information. Is there a better way to do this?
 Simple Example of Code is:
DECLARE MyVARABLES 1 to X
DECLARE c1 CURSOR
FOR
SELECT MyData1, MyData2 to X
FROM MyTable
FOR UPDATE OF MyUpdateData
--Start Cursor
OPEN c1
FETCH NEXT FROM c1
INTO MyVariables
--LOOP
WHILE @@FETCH_STATUS = 0
BEGIN
-----------------------
-- Get other rows data to add to this rows data ......GUESSING THIS IS THE SLOW PART as the table is LARGESELECT MyVar1 = MyData1
FROM MyTable
WHERE MyTableColumns = MyVariables AND MyTableColumns2 <> MyVariables2  --FINDS OTHER ROW (I have three of these)   

--Calculate & Update
If MyVarable = 'this or that'
BEGIN
UPDATE MyTable
SET MyUpdateData = MyVar1 * x *y WHERE CURRENT OF c1
END
-------------------
-- NextFETCH NEXT FROM c1
INTO MyVarables 1 to xEND
CLOSE c1
DEALLOCATE c1

View Replies !   View Related

---

I Need A Faster Query
Hi y'all,
I've recently run a profiler on my code and following query took 7 seconds:
SELECT TOP 10 UI, COUNT(UI) AS Expr1 FROM table WHERE (UI <> 'custom_welcome') GROUP BY UI ORDER BY COUNT(UI) DESC
Is it possible to rewrite this so my code gets faster? It's also possible that it's due to the size of the table?
Thanks in advance! I'll let you know how long your query takes :)

View Replies !   View Related

---

Sqldatasource Faster Than DAL???
Hi all, I m new to this forum and this is my first question. I m having 2 pages in my web site ... page 1 query directly to db using sqldatasource, the second page query through a BLL then DAL by following the step in this tutorial (http://www.asp.net/learn/dataaccess/tutorial02vb.aspx?tabid=63)....  Page 1 is using a "Like" query search and the Page 2 is the normal displaying some product detail.... Under normal circumstances, one will expect Page 1 will be way fastest than the Page 2... however the problem is Page 1 is in thunder speed while Page 2 takes 10 secs to load... 10 seconds is really not acceptable... I really couldnt figure out what happens... both Page 1 and Page 2 are using the same connection string which connection through a DSN....  How is the connection different by using sqldatasource and DAL?? Could someone please help....  ThanksP.S. I m using a Pervasive database 

View Replies !   View Related

---

Transactions A Little Bit Faster
I´ve created a class to make some standard transaction development a little bit faster. The destructor seem to run, but something makes this object slow down the database, if SqlTransaction and/or SqlConnection isnt manualy handled with the method Commit(). Any ideas on how to handle the SqlTransaction and SqlConnection better?

public class DataTransaction
{
private bool blnError = false;
private ArrayList arrErrorList = new ArrayList();
private SqlConnectionobjConnection = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["ConnectionString"].ToString());
private SqlTransactionobjTransaction;

public DataTransaction()
{
objConnection.Open();
objTransaction = objConnection.BeginTransaction();
}
~DataTransaction()
{
if(objTransaction.Connection != null)
{
objTransaction = null;
objConnection.Close();
}
}

public int ExecuteNonQuery(string Query)
{
int intRowsAffected = -1;

SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction);

try
{
intRowsAffected = objCommand.ExecuteNonQuery();
}
catch(Exception e)
{
blnError = true;
arrErrorList.Add(e.ToString());
}
finally
{
objCommand.Dispose();
}
return intRowsAffected;
}

public System.Object ExecuteScalar(string Query)
{
System.Object objToReturn = null;

SqlCommand objCommand = new SqlCommand(Query, objConnection, objTransaction);

try
{
objToReturn = objCommand.ExecuteScalar();
}
catch(Exception e)
{
blnError = true;
arrErrorList.Add(e.ToString());
}
finally
{
objCommand.Dispose();
}
return objToReturn;
}
public bool Commit()
{
if(!blnError)
{
objTransaction.Commit();
return true;
}
else
{
objTransaction.Rollback();
return false;
}
objConnection.Close();
}
}

View Replies !   View Related

---

Faster SQL Query
Hi!
I M basically an application developer & use simple sql queries in my programmings. I do not have much idea abt tuning/auditing part & thatswhy i m unable to answer them properly in my interviews. Can anybody give me some tips?????

Question 1:
In a stored procedure, One SELECT stmt is there & depending upon the @rowcount, it updates around 14000 records which is also written inside this stored procedure. Instead of writing this way, there is some other way which is faster than this. Can anybody tell me the correct way???

Question 2:Can anybody give me few examples like this?????? I need them desparetly.

Thanx. Bye.

View Replies !   View Related

---

Which WHERE Clause Would Be Faster?
Hypothetically,

If I had a WHERE clause that had to compare a string to another string would it be faster one way or another if I broke it down to three different, smaller searches?

An example:


WHERE a = 'abc'
OR a = 'def'
OR a = 'ghi'

as opposed to:

WHERE a = 'abcdefghi'



ddave

View Replies !   View Related

---

Does A Job Execute MUCH Faster???
Hi,

Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.

However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLe job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs.

Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.

I'll appreciate comments. Thanks

View Replies !   View Related

---

Select Into Really Faster
HelloI need this really faster in mS SQL 2000Usernumber (int)reportid (FK)reportreportid (PK)Category (int)SELECT A, B, C, D INTO UserCopy FROM UserWHERE User.reportid IN (SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Category) AND user.number NOT IN (120,144,206,345,221,789,548,666,1204,4875,22,135, 777,444)can return a more than 1000 rows (an the table = 10.000 rows): SELECT MAX(report.reportID) AS maxReport FROM Report GROUP BY report.Categoryand the table user has a few millions rowsReport.ReportId is a Primary key for User.reportid (FK) for the moment it takes up to 3 minutes, i need to do that in 30 seconds maximumthank you for helping

View Replies !   View Related

---

Does A Job Execute MUCH Faster???
Hi,

Previosuly I was executing 2 DTS packages one afte the other manually and together they took a CONSIDERABLE time. The 1st one was pulling data from the OLPT, doing transformations and populating the tables in my Datamart and the 2nd one was doing a FULL process of all the dimensions and cubes.

However I tried scheduling the DTSs as jobs and havethen merged the 2 resulting jobs as a SINGLE job having 2 sequential steps. To my surprise the resulting job takes less than half the time (actually even lesser) as compared with my original approach i.e. running the DTSs. And I am talking about major improvement in terms of completion of the tasks here :)

Am i getting over excited here or is this natural? I assume that if this is correct then jobs much be some sort of "compiled" version as compared to DTS and maybe that's why I have this terrific improvement in terms of execution times.

I'll appreciate comments. Thanks

View Replies !   View Related

---

How To Tell If One Query Is Faster Than Another
I have rewritten a stored procedure that consists of a single select that selects from a view. Essentially I combined the select in the view and the select in the sp into one select. I am now trying to determine if the new version is faster.

The estimated execution plan gives a ratio of 96% : 4% in favour of the new version when I run them together from a query window but when I try to time them I can't get a satisfactory result.

If I run each query once and display the difference between start and end time, they display 0. If I run each one 100, 200, etc times I get different results each time.

Is there anything I can do?

View Replies !   View Related

---

How Can I Make It Faster?
I recently installed SQL2005 Express on a Dell Precision workstation to be accessed by 6 users.  The PC has XP Pro, 2GB memory, and 2.0GHz core2due CPU.  This PC is dedicated for the database only.
I also set XP Performance options in System Properties to Background services and System cache.
Now, the users are experiencing slow response from the database.
How can I make the database response faster? 
Thanks for your input.

View Replies !   View Related

---

Copy And Delete Table With &"Foreign Key References(...,...) On Delete Cascade?
Hello:
Need some serious help with this one...

Background:
Am working on completing an ORM that can not only handles CRUD actions -- but that can also updates the structure of a table transparently when the class defs change. Reason for this is that I can't get the SQL scripts that would work for updating a software on SqlServer to be portable to other DBMS systems. Doing it by code, rather than SQL batch has a chance of making cross-platform, updateable, software...

Anyway, because it needs to be cross-DBMS capable, the constraints are that the system used must work for the lowest common denominator....ie, a 'recipe' of steps that will work on all DBMS's.

The Problem:
There might be simpler ways to do this with SqlServer (all ears :-) - just in case I can't make it cross platform right now) but, with simplistic DBMS's (SqlLite, etc) there is no way to ALTER table once formed: one has to COPY the Table to a new TMP name, adding a Column in the process, then delete the original, then rename the TMP to the original name.

This appears possible in SqlServer too --...as long as there are no CASCADE operations.
Truncate table doesn't seem to be the solution, nor drop, as they all seem to trigger a Cascade delete in the Foreign Table.

So -- please correct me if I am wrong here -- it appears that the operations would be
along the lines of:
a) Remove the Foreign Key references
b) Copy the table structure, and make a new temp table, adding the column
c) Copy the data over
d) Add the FK relations, that used to be in the first table, to the new table
e) Delete the original
f) Done?

The questions are:
a) How does one alter a table to REMOVE the Foreign Key References part, if it has no 'name'.
b) Anyone know of a good clean way to get, and save these constraints to reapply them to the new table. Hopefully with some cross platform ADO.NET solution? GetSchema etc appears to me to be very dbms dependant?
c) ANY and all tips on things I might run into later that I have not mentioned, are also greatly appreciated.

Thanks!
Sky

View Replies !   View Related

---

Delete Syntax To Delete A Record From One Table If A Matching Value Isn't Found In Another
I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

 

View Replies !   View Related

---

SQL - Cascading Delete, Or Delete Trigger, Maintaining Referential Integrity - PLEASE HELP ME!!!
I am having great difficulty with cascading deletes, delete triggers and referential integrity.
 
The database is in First Normal Form.
 
I have some tables that are child tables with two foreign keyes to two different parent tables, for example:

               Table A
            /               
  Table B                Table C
                           /
                Table D
 
So if I try to turn on cascading deletes for A/B, A/C, B/D and C/D relationships, I get an error that I cannot have cascading delete because it would create multiple cascade paths. I do understand why this is happening. If I delete a row in Table A, I want it to delete child rows in Table B and table C, and then child rows in table D as well. But if I delete a row in Table C, I want it to delete child rows in Table D, and if I delete a row in Table B, I want it to also delete child rows in Table D.
 
SQL sees this as cyclical, because if I delete a row in table A, both table B and table C would try to delete their child rows in table D.
 
Ok, so I thought, no biggie, I'll just use delete triggers. So I created delete triggers that will delete child rows in table B and table C when deleting a row in table A. Then I created triggers in both Table B and Table C that would delete child rows in Table D.
 
When I try to delete a row in table A, B or C, I get the error "Delete Statement Conflicted with COLUMN REFERENCE". This does not make sense to me, can anyone explain? I have a trigger in place that should be deleting the child rows before it attempts to delete the parent row...isn't that the whole point of delete triggers?????
 
This is an example of my delete trigger:
 
CREATE TRIGGER [DeleteA] ON A
FOR DELETE
AS
Delete from B where MeetingID = ID;
Delete from C where MeetingID = ID;
 
And then Table B and C both have delete triggers to delete child rows in table D. But it never gets to that point, none of the triggers execute because the above error happens first.
 
So if I then go into the relationships, and deselect the option for "Enforce relationship for INSERTs and UPDATEs" these triggers all work just fine. Only problem is that now I have no referential integrity and I can simply create unrestrained child rows that do not reference actual foreign keys in the parent table.
 
So the question is, how do I maintain referential integrity and also have the database delete child rows, keeping in mind that the cascading deletes will not work because of the multiple cascade paths (which are certainly required).
 
Hope this makes sense...
Thanks,
Josh
 
 
 

View Replies !   View Related

---

Faster Query Results
Can anyone give me the basics of speeding up reports that use queries or views or nested views?
Current reports are now taking over 2 minutes to show.We have thousands and sometimes even millions of records to report against.Queries have 4 and 5 table joins etc.
We are using ASP.NET 2.0 in Visual Studio 2005 and Crystal Reports.
Thanks
 

View Replies !   View Related

---

VarChar Or Char Faster?
Does using varchar or varchar(max) affect the system performance?
Should I use fixed size coloumns to increase speed?

View Replies !   View Related

---

Which Is Faster And Less Server-intensive?
File I/O or SQL Database calls?Note that my SQL Server is on a separate server, so that cuts down on the web server resources needed per query, but it increases the time necessary for the query to happen.

View Replies !   View Related

---

What Is Faster... INSERT Or UPDATE?
I have a situation where currently, I check to see if a record exists and I do an update if it does, I do an INSERT if it does not. This happens about 200k times each night.

I'm trying to speed the application up that is doing this and I was wondering if it would be a better idea to just delete the records I'm about to recreate, before I start, then do an unconditional insert.

It would eliminate my IF EXISTS check but if an update is faster than an insert, it might even out.

Also, will SQL deal with the big delete and the data being recreated each night without too much bloating or fragmentation?

View Replies !   View Related

---

What Is Faster: Dynamic Sql WITH Or WITHOUT Parameters?
Here is he issue.

* We have a DAL that generates all SQL dynamically out of a nobject model. Standard very powerfull O/R mapper.
* In the DAL, for CRUD operations, we generate the statements dynamically. As an example, let's take INSERT.
* The insert is generated ONCE, with parameters, and cached. For every reuse, the parameters are replaced (in value), and the whole thing commited.

I see hte following negative: I can not easily batch multiple inserts. Parameters have to be unique per batch. So, if I want to batch two inserts, I need two sets of parameters.

Alternative:

Instead of generating the SQL with parameters, we generate the SQL as a string ready to be inserted for / with a String.Format, and then I encode the parameters and make one SQL String out of this. Now, please - don't say "sql injection", we are not that stupid, the layer handles this already, properly encoding all dangerous values.

With this approach, the SQL statement would be a string and not use any parameter. As a result, I could batch them up as much as I want (ok, up to a certain string size). I need to keep parameters around anyway (for blobs etc.), but most objects do not have blobs, and the SQL is prettty small. This small SQL could be batched significantly (100 statements per batch, propably mode) and be submitted to the database. As a result, the round trips to the databae would go down.

Now, my question is - which of the two approaches is more advisable, from a performance point of view? Again, stuff like SQL injection and ease of handling are totally irrelevant - the SQL never leaves the DAL and is generated in there, and we will go through a lot of complexitiy for higher performance.

Normally I would say batching should be better. SQL Server can auto-parameterize the statements (reusing the query plan), and / but the network round trips are the larger issue here.

Any comments on this?

View Replies !   View Related

---

Compond Index And Key Faster/better?
In a situation where you have two tables in a hierarchy like this:create table authors (authorid int identity (1,1))create table books (authorid int,bookid int identity (1,1))Is there any disadvantage to having the primary key and the clusteredindex as a compound key, like this:alter table books add constraint PK_books primary key clustered(authored, bookid)Normally, I would make bookid the key, but then I got to thinking, mostof the queries are going to be "select * from books where authorid =@@some_authorID"So, wouldn't a compound key and index make this a little faster?

View Replies !   View Related

---

Temp Table Faster?
If you were doing paging of results on a web page and were interestedin grabbing say records 10-20 of a result set. But also wanted to knowthe total # of records in the result set (so you could know the total #of pages in the set).Would it be better to query the DB table 2X. Once for Count(*). Andagain for the records for the current page?Or better to create a temp table, select the records into it, and thenget count(*) and the page results from the temp table?I saw an example in a book that made a temp table to do this and to meit seemed like it would be slower. I don't get the reason for a temptable. Anyone have any ideas?

View Replies !   View Related

---

Faster Count(*) Or Alternative
hi,I have a table with several million rows.Each row is simply the date and time a certain page was viewed.egpage1 1-1-00page2 2-1-00page1 16-1-00page1 17-1-00page2 19-1-00I now need to find the most popular pages so I do this...SELECT TOP 10 COUNT(*) AS mycount FROM tblPageViewsGROUP BY place ORDER BY COUNT(place) DESC....which gives me the top 10 most viewed pagesbig problem - this is getting slower and slower as the table grows andgrows.what should I do?is there an alternative?I think I need all the rows (rather than a simple incrementingcounter) because I might want to say "what was the most popular pageon a certain day or within a certain period"tiaTim

View Replies !   View Related

---

Making My Views Faster
How can I make my views faster from Access to SQL Server? I have a 6 tables each with a "Select *" view. That way the Access programs do not have update capability on the original tables. Note: I have created the views with the same name as the original tables, deleted the old tables and relinked the views. The original tables are much faster than going through the views.

View Replies !   View Related

---

Clearing Out Tempdb Faster?
Hello:

We have been testing a new vendor_purchased application and are now
running some month-end processes/jobs now. One of the processes that we
kicked off stopped when a 2gb tempdb filled up with only 5mb or so remaini
g. In reviewing what happened, I noticed that tempdb is in the process of
clearing itself out slowly when we restarted the process. The database has
80% of its disk space remaining and the transaction log has also 75%
remaining.

My questions are:

1) Although I don't think so, is there nay way of speeding up the process
of tempdb clearing out the data in it?

2) We will need to examine what sql code the vendor has used that caused
this to happen. Aside from group by and order by, if there are a lot of
'select into' code, what alternatives do we have?

Any information that can be provided will be fine. THanks in advance.


DAvid Spaisman

View Replies !   View Related

---

Faster Restoration Of Backup.
Hello friends,

Kindly guide, if you are aware of any mechanism for restoring the differential backup only.

This can save a lot of time other than restoring complete database.

Thanks in advance.

Shrirang

View Replies !   View Related

---

What Is Faster, BCP, DTS, Bulk Insert?
We intend to import up to 20+ GB of data into a database that will be a snapshot of month end data. The database tables must be dropped and then recreated just prior to importing the data. We are investigating BCP, DTS, and Bulk Insert. What is the fastest method for importing data given our approach?

View Replies !   View Related

---

How To Make Insert Faster
Hi.We have stored procedure update specific table
Each time it run it delete 5000- 6000 rows
from table then insert 5000- 6000 rows with different information.
It take up to 1 1/2 min execute.

1.Can force Sql server do not make entry for each insert and if yes would it increase speed of procedure ?
2. Is any other way increase speed of insert?

View Replies !   View Related

---

Help Make Query Run Better (faster)
I need help makeing the following query run more efficently.


Code:


SELECT t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
FROM Landlord_tbl t1
left outer join Mail_tbl t2
ON t2.potentialSitesID = t1.potentialSitesID
WHERE t2.mailed_out_date is null and NOT(t1.firstName+t1.lastName) is Null
GROUP BY t1.ID,t1.firstName,t1.lastName,t1.address,t1.city,t1.state, t1.zip,t1.locationAddress,t1.locationCity,t1.locationState,t1.locationZip
ORDER BY t1.firstName, t1.lastName, t1.city, t1.state

View Replies !   View Related

---

Better (faster) Way Of Handling Query
 

I have a query which will display the employee name who is handling maximum number of project.
the query is



Code Snippet
 
select EmployeeDetails.Empid,

EmployeeDetails.FirstName + ' '+EmployeeDetails.LastName as EmpName,

count(*) as Number_Of_Projects
from EmployeeDetails left outer join LUP_EmpProject

on EmployeeDetails.Empid= LUP_EmpProject.Empid
group by EmployeeDetails.Empid,EmployeeDetails.FirstName,EmployeeDetails.LastName
HAVING count(*)=(SELECT TOP 1 COUNT(EmpID)



FROM LUP_EmpProject GROUP BY EmpID ORDER BY 1 DESC)
 
 




where EmployeeDetails table contain empname and empid. LUP_EmpProject table contain empid and project id.
 
Can anybody help me in writing a better query than the above given?
 
Please help!!!!!!!!!!

View Replies !   View Related

---

Would Split Make It Faster ??
Hi,
Here is the typical processing I do in Data Flow
1) read data file (data source)
2) do something with the data (e.g. derived column, script component, etc)
3) pass data as parameters into OLE DB command (stored proc) to take care of the rest.
 
Would it be faster if
 I split the data into say 10 "chunks" (using split transform).  Each chunk will go to its own OLE DB command (10 OLE DB command).  Now I will have 10 concurrent processes running the stored proc ?
 
Am i misunderstanding concurrent process here ?
 
Thanks
 

View Replies !   View Related

---

Why SQL2005 Is NOT Faster Than SQL2000?
Hello!!

we use SQL2000(64bit) and we are considering our system to apply SQL2005

so i tested two SQL-Server on One Machine by installing each instance of them.

The RESULT..

i can't find difference of performance between two instance.

is there no impoving in Database engine?

 

* i'm sorry, for my poor sentence...

 

View Replies !   View Related

---

Can I Make Subreport Run Faster?
 

Hi:

I have thousands of rows that I have to retrieve for my report. When I run my main report it returns my data as a reasonable time.

When I run my sub report (by its own) again data are returned in reasonable time.

When I build the sub report in my main report it takes forever (I have to stop). Are there any tricks that I can use to make this run faster?

 

 

Thank you in advance

Bahram

 

View Replies !   View Related

---

Faster Remove Duplicate SQL
I have a table containing over 100,000 email addresses. This email table gets duplicates in it, and our customers don't want a second (or third or fourth) copy of our news letter. To prevent this, we run the following SQL to kill the duplicates:





Code Snippet

DELETE FROM _email WHERE _email.eid IN
(
    SELECT tbl1.eid FROM _email AS tbl1 WHERE Exists
    (
        SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1
    )
)
AND _email.eid NOT IN
(
    SELECT Min(eid) FROM _email AS tbl1 WHERE Exists
    (
        SELECT emailaddress, Count(eid) FROM _email WHERE _email.emailaddress = tbl1.emailaddress GROUP BY _email.emailaddress HAVING Count(_email.eid) > 1
    )
    GROUP BY emailaddress
);
This query takes about 2hrs to run which is really hurting our server preformance. Is there any way to do this faster?

I am running SQL Server 2000

Thanks in advance

View Replies !   View Related

---

DROP Vs TRUNCATE Which Is Faster?
I work with datafiles containing more than 1 million records. Which would be faster DROP TABLE or TRUNCATE TABLE?

View Replies !   View Related

---

Query Run Time (which Should Be Faster?)
Hi, when I execute the sql in DBTestArchive and then DBTest query analyzer , I found that the run time is shorter in DBTest. Can I make assumption that the query run time is shorter in DBTest if I select data from it and insert into another database? Thanks.



INSERT INTO DBTestArchive.dbo.tblVendorMasterArchive
SELECT * FROM DBTest.dbo.tblVendorMaster

View Replies !   View Related

---