Many DELETE As Fast As Possible

Oct 23, 2006

hello all !
for MS SQL 2000
i am having a table with > 100 000 rows
I must clean it

DELETE FROM myTable WHERE Name LIKE 'aser%' AND info IS NULL
DELETE FROM myTable WHERE Name LIKE 'tuyi%' AND Info = 'ok'
DELETE FROM myTable WHERE Name LIKE 'hop%' AND info LIKE 'retro%'
.....

about 20 DELETE commands

what is the best way to do it ?


thank you

View 14 Replies


ADVERTISEMENT

Need Lil Fast Help

Jan 17, 2006

my system has 2 db's - sql server 2000 & db2 @ separate locations. i have a select query which needs 2 pick up consolidated data from both the tables. also the schema on the db2 has minor changes when compared with the schema on sql server 2000.

while searching on microsoft i came across the technique of creating a linked server. would this be possible 2 implement in my scenario. also would in this case, be advised that i create another view in the db2 server which has changed the db2 schema to the sql server schema format??

please hurry..
regards,
sameer

View 2 Replies View Related

Need Help Fast :(

Aug 6, 2007

One or more files listed in the statement could not be found or could not be initialized. (Microsoft SQL Server, Error: 5009)

I accidentaly created a log file on my drive E:, but every time that I try to delete the log file it keeps on returning the same error.
Can someone please help me delete the log file.

View 7 Replies View Related

Best Way To Get Queries Fast

Aug 17, 2007

hii have over million records in my DB, what is the best way to get the results fast in case i need to get details of an employe name say "robert", if i do it normally it will take long, should i use index or is there any other good way.thanx in advancecheers         

View 1 Replies View Related

How Set Up 40 GB Db For Fast Recovery

May 28, 2002

Hello everybody .
I have 40 GB db running mostly transaction processing.
I set up
1. back full backup 2 times a day (takes 30 -40 min)
2. log backup every 15 min
3. custom log shipping
4. We don't won't use Cluster.

Once in while becouse of nethwork, or other problem log shipping fails,
so I have to restart log shipping all over starting from restore in stand by mode last full back of my db.IT takes 2-3 hrs just to do this restore !!!

1. So I am asking advice is any way I can bring down time for restore ?
2. Should diffrential backup be taken ?
3. We will not use Custer

Alex

View 4 Replies View Related

InNeed Of Help Fast Please!!

May 3, 2008

Hi everyone im in deep in need of help in a very easy query and few questions i want to ask,, i use msn boy22202@hotmail.com please i want to contact anyone who use sql server 2005 that can help me in it.... thank you

View 4 Replies View Related

FAST Insert ?

Dec 18, 2007

I need to insert data to a temp table in SQL ,
I have

CREATE TABLE TMP_X (
doc_name varchar(200)
)


--select * from TMP_X

INSERT into TMP_X
values
(
'...,



but its saying there isn't a match, and i know why its trying to insert all the data as one row, but i need them as seperate rows as i want only 1 column..
is there another INSERT type function ?

View 2 Replies View Related

Fast Insert

Feb 14, 2008

If I have a table with one column
and i want to insert a few 100's rows of names
I can't use the INSERT stmt as that does one row at a time ,
how can i achieve this ?

View 5 Replies View Related

Deleted Default DB, Need Hlp Fast!

Mar 27, 2004

I have stoopidly enough deleted default Db. That causes Enterprise Manager to be unable to work with my DB's. The default DB I deleted had no other functions other then being default DB, I mean it was outdated, and I had other DB's that contained all my importent work. They are still running, and I can view DB driven site hosted at localhost, even though default DB no longer excist. I am even able to upload new content, or add new users, so this means all my other DB's are fine. I can even see SQL server icon in my bottom right corner of my desktop, and it shows server running.

Now I am in the need of adding tables and rework some of my excisting tables and stored procedures, but I am not able to do that with Enterprise Manager, due to the lack of default Database.

How do I correct this problem? I have gotten one tip of doing the following: EXEC sp_defaultdb 'User', 'DB' but I am not sure what to do with this.....tried to run it from command line, and put my username and the DB I would set to default but nothing happend.

So I need more details, step-by-step guiding will work, as I don't know a hole lot about Enterprise Manager and SQL.

Btw, this is my error in Enterpr.Managr:

A connection could not be established to MyComputerVSDOTNET2003

Reason: Cannot open default database. Login failed..

Please verify SQL server is running and check your SQL server registration prpoerties and try again

Pls tell me there is a way to fix this problem.

View 6 Replies View Related

Fast User Interface

Dec 14, 2001

Hello everybody,
please advice: what is the fastest standard method of user interface access to SQL database? I am looking for fast display of one master record plus related dependent records, plus fast scrolling through master records with display of dependent records as fast as posible. Perhaps a standard problem with standard solution? At current state of matters, I am still much slower then with my old Access97 database.

thanks for any advice,
Otakar Kverka
Prague

View 1 Replies View Related

TempDB Growing Fast

Sep 10, 2003

I notice this morning that my tempdb grows very fast. I have 26GB in my
hardrive and all the space occupied by tempdb and finaly the qeury got failed due to 0 space in hardrive and there is no space to grow tempdb.
The select query supposed to bring about 40000 rows.
I ran this same query in different server that is not growing even 1 mb.
I checked the tempdb option the Trunc log on checkpoint is true.

Why this problem happening ?.
I have just dbo permission to access all the database.
Do you have any advice regarding this?.
Thanks,
Ravi

View 3 Replies View Related

How To Move Data Fast

Jan 23, 2004

Hello everybody
We need to move table T1 from database A to T1 database B on same server

size of table T1 15 GB and 40000000 rows

database B just created and will act as warehouse

could it be done simply by
1.creating table T1 on db B and then
2.set db to simple recovery
3.
insert into B.dbo.T1
select * from A.dbo.T1
4. create all the indexes on table T1 in db B

free disk space is 35GB

Any idea how to optimze import
Thank you

View 5 Replies View Related

The Fast Way To Restore Database

Oct 6, 2005

Hello, everyone:

My database backup files are 3-5GB. Restoring always take over 20 minutes. Is there the fast way to restore the big database?

Thanks

ZYT

View 5 Replies View Related

Indexes Degrade Too Fast

Jan 13, 2006

(Win2003, SQL Server 2000 SP4)

I have a database of about 5Gb of size. Some queries where taking more than 1 minute to complete execution (all of them are stored procedures). Because of that lack of performance, I call the command DBREINDEX for each table, executed the sp_updatestats system stored procedure and finally I executed the sp_recompile system stored procedure for each sp in my database.

After all this task, queries completed in a matter of a few seconds instead of minutes. Strange enough is that some hours later (about 6 hrs), after normal use (this database belong to a Client/Server information system), the problem appeared again: Queries started to take too long to complete.

I am assuming that indexes are degrading too fast so that they required another ReIndex, but I am not sure.

Any thoughts? How can I prevent this behaviour?

Thank a lot in advanced.

View 5 Replies View Related

2005 Too Fast Problem!!!

Sep 5, 2007

I have a weird situation I had not expected.....

I insert a record to a table and "later" I update it.
I have two fields to capture time information: Created and LastModified.
My update is very simple: update .... set ..,[LastModifiedDate] = GetDate() where id = @pId.

Now my problem is that I am seeing the created and lastmodified times as the same (in format 2007-09-05 12:38:42.383) !!??!

The record has definitely been updated (other fields are populated).

Can anybody enlighten me?

View 14 Replies View Related

Seeking For The Fast Query

May 11, 2004

Hello, everyone:

There is a big table with several million records. I am developing a query that retrieve the first rowset that meets WHERE condition. Any suggestions for the fast query? Thanks a lot.

ZYT

View 2 Replies View Related

Sp Fast Loop Through Resultset

May 16, 2008

Hi,

I have made some stored procedures to check if a user is involved with a certain record. basically every stored procedure contains the following logic.

example spCheckClientRelated:
select @res = count(*) from client_role where client_id = @cid and employee_id = @eid

if (@res = 0)
begin
... next select
end
if (@res = 0)
begin
... next select
end
....
return @res
end

so far so good. But the final check in CheckClientRelated tests if a user is related to one of the sales projects for that client.

I allready have the spCheckSalesProjectRelated that returns 1 or 0 similar to the example above

so I want to find an efficient method that selects all the sales_project_id 's from the sales_project table where client_id = @cid (i use offcourse select @sid = sales_project_id from sales_project where client_id = @cid at the moment)

And then I have to execute the spCheckSalesProjectRelated method for each @sid and @eid. This if offcourse where my problem is located. I don't know how to do a fast check for every selected @sid, until spCheckSalesProjectRelated returns 1

As you probably can determine from my question, sql is not really my domain, and I'm certainly not an expert, but I don't mind reading or looking up some stuff, so even a clue or a direction to look in would be most appreciated

thx in advance.

View 3 Replies View Related

Form Slow, Then Fast

Oct 16, 2005

I have a very puzzling situation with a database. It's an Access 2000 mdbwith a SQL 7 back end, with forms bound using ODBC linked tables. At ourremote location (accessed via a T1 line) the time it took to go to a recordwas very slow. The go to mechanism was a box that the user typed the indexvalue into a combo box, with very simple code attached:with me.RecordsetClone.FindFirst "[Index] = " & me.cboGoToIf Not .NoMatch ThenMe.Bookmark = .BookmarkEnd Ifend withNow, one would say that going to a record is slow because I'm using..FindFirst over a T1 line. And that's what I thought. However, as I wasworking with the form, commenting out various sections not related to the GoTo, I found that the Go To functionality changed, though I didn't modify thecode.Previously, going to a record near the end of the 50,000 record recordsettook about 1-2 seconds, but going to a record near the beginning, took about20 seconds. After the form changed, going to any record in the recordsettook about 1-2 seconds.So the question remains: why did it take so long to go to a record near thebeginning of the recordset, but not near the end (and the ones in the middletook an amount of time about halfway between the two), and what changed sothat now the form is working fine for all records?I've compared the changed form with the previous copy, and I don't see anydifferences. I've compared all code in the form module, and I've comparedall form properties. The forms are identical as far as I could tell. Butsomething happened as I was commenting/uncommenting code in the form thatgot rid of the problem with it taking a long time to go to some of therecords.My first thought was that something got recompiled, and now the form isfast. So I went back to the original version and changed some code andrecompiled, also did a compact and repair. But it was still slow. I alsotried doing an explicit decompile and then recompiled it. But it was stillslow.So this is very frustrating that the form is now working fine, but I can'tsee anything that's changed. If I don't see why the form is now fast, thenthere's no reason to believe that it might not at some point go back tobeing slow again. And then I'd just have to hope that something changes. Itwould be good to figure this out.Any ideas as to what might have changed here to cause the form's Go To to befast would be appreciated.Thanks,Neil

View 8 Replies View Related

Fast Look Up Of Long (n)varchar

Mar 6, 2006

I have a table containing URLs. I want to be able to look up an URL veryfast, so I used an nvarchar to store the URL, and put an index on it(maybe naive).Anyway, I bump into:"The index entry of length 911 bytes for the index 'UQ__URL__1367E606'exceeds the maximum length of 900 bytes."What's the best way to handle this? I want to do the look up fast. Theonly thing I could think up was adding an extra column containing a digestfor the URL, and look up all URLs with the same digest, *and* having thesame value (which could give either 1 or 0 results).I am new to MS SQL, so I might describe a silly solution, basically I wantto look up URLs to ID the fastest way possible.--John MexIT: http://johnbokma.com/mexit/personal page: http://johnbokma.com/Experienced programmer available: http://castleamber.com/Happy Customers: http://castleamber.com/testimonials.html

View 3 Replies View Related

Need A Fast Queue Using A Table

Jul 20, 2005

I am trying to implement a very fast queue using SQL Server.The queue table will contain tens of millions of records.The problem I have is the more records completed, the the slower itgets. I don't want to remove data from the queue because I use thesame table to store results. The queue handles concurrent requests.The status field will contain the following values:0 = Waiting1 = Started2 = FinishedAny help would be greatly appreciated.Here is a simplified script to demonstrate what has been done.CREATE TABLE [dbo].[Queue] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[JobID] [int] NOT NULL ,[Status] [tinyint] NOT NULL) ON [PRIMARY]GOCREATE INDEX [Status] ON [dbo].[Queue]([Status]) ON [PRIMARY]GOCREATE PROCEDURE dbo.NextItem@JobID integer,@ID integer outputASSELECT TOP 1 @ID = [ID]FROM Queue WITH (READPAST, XLOCK)WHERE (Status = 0) AND (JobID = @JobID)RETURNGO

View 6 Replies View Related

Fast Counting Of Records

Jul 20, 2005

I seem to remember reading many moons ago about a function where youcan retrieve a count of the last recordset you opened.For example:I've got a stored procedure that returns a recordset using the TOP 10so I only get the top 10 records. I need to know the recordcount but Idont want to reuse the SELECT statement because its quite complex.Any ideas?What does @@Count do?Thanks in advance

View 1 Replies View Related

Fast Record Count

Mar 21, 2007

I think I am asking this in the right place.

I'm trying to get a record count from a table with 30million items and it takes forever.

Here is my code:

SELECT COUNT(f_id) AS 'ROWCOUNT' FROM tablename

Is there a faster way.

BTW f_id is primary key indexed.

Thanks

View 5 Replies View Related

Templog Is Growing Too Fast

Dec 1, 2007



Templog is growing 1 GB per hour.

I've read some articles about this issue, that talk about how to shrink it.

In this case I need to find out what and why this is happening

How can I monitore it?

I know, sometimes, I exaggerate in using temporary files, in order to make reports faster.

The Tempdb size is normal.

Thanks

View 4 Replies View Related

Job Activity = Slow, Non Job = Fast

Oct 10, 2006

I have created an integration services package with a script-source reading data from Active directory. Pretty much data is read and written into a sql2005 database.

I have notised that when I run the package via right-clicking the package (Under stored packagesMSDB) and selecting "Run Package" it takes about 45 minutes for it to complete successfully. The same goes if I run the package from inside Visual Studio. However, when I create a job and put this package as one of it's tasks the task takes about 1h 40min. This is more then double the time! In neither case the server is occupied with soething else. And I have tried it several times so it wasn't just an "accident".

Any idéa of what this depends on?

regards Andreas

View 9 Replies View Related

Data Access :: Why Bcp Out Is Fast

Jun 10, 2015

why bcp out (exporting data to a text file from a sql table using bcp utility ) is faster ?

View 6 Replies View Related

DataBase Recovering Please - Need Fast Reply

Nov 23, 1999

What should I have done? Is there anything that can be done other than restoring from backup?
How does one know if the database is really recovering or is EM just joken? I can wait 2 hours
before starting the restore

I was BCPing 12 million rows into a staging table. II used the '-b' option every 20K which I thought
would do a commit and clear the log in batches. After the process EM appeared to show the transaction log
as empty. Upon inspecting the Bcp output file I discovered the message that the BCP did not complete
because syslogs was full. I could not do a truncate transaction log or a dump database. I tried to
do a truncate transaction with no_log and it appeared to just hang. I stopped the SQL Server thinking
I could dump the transaction log, but could not start the Sql Server again. I then stopped the NT Server
because 'if all else fails'. The SQL Server started but the user database if marked as recovering.

View 5 Replies View Related

Why The Database Size Growth Too Fast ?

May 31, 2005

My DB size was from 500MB to 10GB since 8/1998 to 12/2004. But now is 16GB (from 1/2005 - 5/2005), I don't why the data size growth too fast (as double) ?

View 4 Replies View Related

DB Slow On Server But Fast On Desktop

Apr 2, 2008

I'm trying to figure out how to determine why the same database is fast on my desktop but slow on a robust multiprocessor win2003 server.

I have a complex proc that takes about 27 seconds on my desktop sql server but takes almost 5 minutes on the server. The server processors are running at about 1% utilization while the proc is running.

Any ideas on how to troubleshoot this extremely poor performance?

View 8 Replies View Related

Transaction Logs Grow Too Fast

Nov 25, 2005

Hi all,
I'm having a problem with one of our ddbb because we didn't run the maintenance plan from the beginning. The thing is that the hard drive is out of space and the log files are around 100GB. We only have 20MB free. Do you think that is space enought to run the maintenance plan or the shrink command??

Thanks very much!!!

View 1 Replies View Related

Fast, Consistent Way To Bulk Insert?

Oct 17, 2006

I'm looking for a way to insert 50k records into a SQL Server table, and need to get it done faster. right now using BULK INSERT takes 5-10 seconds, but faster would be better, and even better if it were a consistent amount of time.

I've heard of DTS but don't know quite how to use it - would be offer any performance gains? any clue what the bottleneck is for BULK INSERT? hard drive speed? amount of RAM (this was on a 512mb machine)? parsing the fields?

thanks for any ideas.

View 7 Replies View Related

SQL 2012 :: Fast Changing Dimension

May 18, 2015

I have a table that needs to be incorporated into the data warehouse.The table has the following schema.

CREATE TABLE [dbo].[Consignment](
[Id] [int] IDENTITY(1,1),
[BooingID] INT
[BookingDate] [datetime] NULL,
[CarrierServiceName] [nvarchar](255) NULL,
[CarrierServiceCode] [nvarchar](255) NULL,

[code]...

This Table has the same granularity as the fact table as it’s one row per booking.However due to the nature of the data I would not want to incorporate this into the fact table.The Originating and Destination addresses are populated for each booking and are required for reporting.

Question:Should this be moved into a fast changing Dimension table.? or would there be a better way to incorporate this data.

View 1 Replies View Related

Loop For INSERT ? [ Fast Reply Please ]

Jun 23, 2008

Hi everyone
I want to know if it's possible to do a for/while-loop so i can use INSERT

Look:
I've this int [] test = new test[140];
But i need to insert for every value (140) a number
so normally it would be :
INSERT ... (case1, case2, case3 ...) value (test[1],test[2],test[3] ...)
But isn't there a way to it with a loop?
SOmething Like this ?

for( int i = 0 , i< 140, i++) {
INSERT case[i] value test[i]
}

Thanks in advance

Jonas

View 8 Replies View Related

Fast Copy Big Table Content

Jul 23, 2005

Hi all,need advice on the following task:copy the content of a big table from DB_A to DB_B in the same serverthe size of table:~ 7 million rows, ~ 9G in size, 1 clustered pk index, 13 nonclusteredindexcurrent practice:use DTS to copy the data, takes over 20 hours as-- first had to delete existing data of the table in DB_B-- then copy-- all these happen while all indexes are in place.I am trying to check what is the best or most efficient way to copythis kind of data and what wouldbe the expected time for such load.my machine: SQL 2000 Enterprise, 8-way P4, 12G RAM on a EMC Clarrion600 SAN.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved