56 Million Records Search
Jul 20, 2005
Hey folks...
So I have a table that looks like this:
CREATE TABLE [tblStation] (
[CAMPAIGN] [varchar] (8),[LISTNUM] [varchar] (10),
[PHONE] [varchar] (10),
[EVENTTIME] [datetime] ,
[STATION] [int],
[OPERATOR] [varchar] (16),
[EVENTCODE] [varchar],
[CALLSPAN] [decimal](18, 0),
[FDISP] [int],
[RECORDNUM] [varchar],
[STC] [varchar],
[PROMOC] [varchar],
[EXP_CAMP] [varchar],
[PROMO3] [varchar],
[MAXATT] [char],[LISTNAME] [varchar],
[SITENAME] [char],
[Row_id] [int] IDENTITY
It's taking nine seconds to run the following command:
SELECT count([fdisp])
FROM [TrunkFiles_new].[dbo].[tblStation] WITH (NOLOCK)
WHERE fdisp IS NULL
Anyone familiar with a table of this size having performance like
this? The [fdisp] column has a non clustered index on it.
Thanks in advance...
View 1 Replies
ADVERTISEMENT
Apr 23, 2007
Hi
I have a new client with an existing system that has just over 2 million business listings in one table. Each business listing is associated with one business category.
* Company Table (around 20 fields):
companyID
companyName
categoryID
state
postCode
etc.
* Category Table (5 fields)
categoryID
categoryName
etc.
We are using MSSQL 2005 Express Edition with Advanced Services
A free text search needs to be performed on the companyName and categoryName limited by region (state and or postcode).
1) What kind of response times should I expect for the free text search (I have not used the free text search before)
2) How should I index the companyName and categoryName so they are both used in a joined query? i.e. Do I just configure the free text search index on each field separately and it should work?
Any suggestions appreciated.
Best Regards
Kevan
View 2 Replies
View Related
Jun 12, 2015
I have a requirement to delete 1 Million records from a table having 10 Million data and it's being queried on 24/7 basis (don't have a downtime). how can I achieve that?
View 13 Replies
View Related
Jul 30, 2007
I intend to develop a web based application, which uses SQL server 2005 at back end and Visual studio 2.0 as front end.
Application serves two functionalities
Requirement1: It carryout a search (In SQL server) for a particular name entered from front end .net application against a huge DataBase of size about 1 million records.
Scenario: The above requirement can be complemented by following example
Consider we have a bank database which has its existing customer DataBase having containing attributes like Name, Age, and Profession e.t.c.
Now if some new customer want to open a new account in bank, then bank officials want to know whether the
new customer is one of the existing customer or not(without asking to customer itself).
System should be able to detect the combination of name also i.e if we enter "Jhon" from front end .net interface
then application should be able generate all list of all customer having "Jhon" as part of their name at any location(firstname, middlename, lastname).
Requirement 2: If some time change is detected in bank's extisting customer's DataBase then each record of this DataBase is searched against a external dataBase(having almost 2 -3 million records).
Scenario: The above requirement can be complemented by following example
If new user is added to bank's existing customer database(database change) then this new updated database's every record is serarched against another bank's database.
I would like to hear experts voice for database design of such application for optimal performance,and types of searches I should look for application.
View 5 Replies
View Related
Jan 27, 2006
I am currently working on a simple page to insert 1.6 million UK postcode records into an SQL server table. The table has three columns for the postcode, longditude coordinate and lattitude coordinate. The data is sourced from a pipe (|) delimited txt file and inserted into the database using a FOR loop. The problem I have is that the page will hang after inserting only 10,000 records, the page displays either an invalid View State error or a page cannot be found error.
Now I assume the viewstate error stems from the fact that there is a form on the page which simply contains a button to execute the script and a few labels to show the progress. But without the form and associated viewstate the insert still fails to complete.... any ideas?? Would I be better running this on a thread or should I just do it in stages and be patient. I have now modified the page to read the database on load and pick up from where it crashes?
View 2 Replies
View Related
Aug 30, 2006
Meg writes "Hi,
I have a table that has 4+ million records. I need to update those records. I am facing some performance issue. Can someone please advice?
update stage
set batch_status = 1
where update_status = 0
Update transaction
Set aId = s.aId,
b = s.b,
from stage s
Where s.aId = transaction.aId
and s.batch_status = 1
Update stage
Set update_status = 1,
batch_status = 2
where
batch_status = 1
When I run the above query with "set rowcount 1000", it runs in one minute. When I run the query for "set rowcount 10000", it runs in 1 hour 56 minutes. Can someone help me to optimize it?
Thanks.
Meg"
View 4 Replies
View Related
Nov 16, 2001
How well SQL Server can support 300 million records...
Any body is working on big database like this. can anyone give me some input on this. it's going to be 60GB database size.
View 1 Replies
View Related
Mar 26, 2004
i have a directory database with approx. 80 million records. i am feeding the database with bulk_insert. Indexing one of the fields took about 8 hrs. After indexing when i run queries with the indexed field the response time is under 1 sec. However if i run select queries with like on non-indexed fields it takes more than 2 mins. So i decided to index 4 other fields in the database and it looks like the indexing process is going to run for 2 days.
i am a novice in SQL database design and i am not sure if this is the best way to index the table. i am just using create index. Any suggestions / advice welcome.
View 5 Replies
View Related
Mar 19, 2008
Hello,
What is the fastest way to update 20million records in our database.
I have tried to do a simple update statement like this:
update trail_log with (tablockx, holdlock)
set trail_log .entry_by = users.user_identity
from users
where trail_log.entry_by = users.user_id
but it take 10 plus hours to run since it cannot commit the transactions until the very end. So was was thinking that I need to commit in batch like after 50K but that is slow as well.
Set rowcount 50000
Declare @rc int
Set @rc=50000
While @rc=50000
Begin
Begin Transaction
update trail_log With (tablockx, holdlock)
set trail_log.entry_by = users.user_identity
from users
where trail_log.entry_by = users.user_id
and trail_log.entry_by not like '%[0-9]%'
Select @rc=@@rowcount
--Commit the transaction
Commit
End
go
I have let the above statement run for 1.5 hours and it only update 450000 rows. Any ideas...
Maybe I'm doing it wrong. Please Help!!
View 1 Replies
View Related
Oct 12, 2007
Hi all,
I have a sql script that updates records in a table with 40 million records.
There is some functionality in the script that could be put away in functions for code reuse/elegance.
Functions would cause execution overhead.
What else could I use besides functions that would allow me the code reuse and not compromise the execution over head? Is there any thing like includes in TSQL that would allow me to do so?
TIA..
View 4 Replies
View Related
Mar 18, 2014
I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.
SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)
My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2
View 7 Replies
View Related
Sep 23, 2014
I come from a web based world were loading 1.5 million records into a temp table is suicide. I’m doing more data warehouse stuff now and I was looking into optimizing a buddies proc and noticed he was loading 1.5 million records into a temp table. We had a discussion about it because being from a web world I was drastically against it. He on the other hand didn’t feel it was an issue being it gets called once maybe twice a day. The tempdb is set to autogrow and it is on a different drive than all the other databases on the box. It has one ldf and mdf. He’s creating an index on the table after load. Why we shouldn’t be loading 1.5 million recs into temp table?
View 5 Replies
View Related
Apr 8, 2008
Hi
I have 2 tables with more then million records in each and I have to perform full outer join.
The problem is that the join clause contains 2 different parameters (int and string) like this:
Select *
From a full outer join b
On a.cli = b.cli OR a.reference = b.reference
Because of the OR in the clause and the million records the query is infinite. If I change to one rule only then it works fine.
How can I join these 2 big tables with 2 rules?
Thanks
Itay
View 2 Replies
View Related
Jul 21, 2015
I am trying to update a large table which consists of 45 million records , it is taking more than 2 days to the update , below is my approach
1. The table has only one clustered index and no other indexes on the table.
2. I am updating in batches say 20000 record-wise.
3. Changed the recovery mode to bulk logged and auto-growth size is set to 300MB and there is enough space in my disk for transaction log .
But still the query is running slowly.
View 10 Replies
View Related
May 18, 2006
I have tried to process > 3 million Fuzzy grouping records on two different servers with no success. 3 mill works but anything above 4 mill doesn't. Some background:
We are trying to de-dup our customer table on: name (.5 min), address1 (.5 min), city (.5 min), state (exact). .8 overall record min score.
Output includes additional fields: customerid, sourceid, address2, country, phonenumber
Without SP1 installed I couldn't even get a few hundred thousand records to process
Two different servers - same problems. Note that SSIS and SQL Server are running locally on both
The higher end server has 4GB RAM, the other 2.5 GB RAM. Plenty of free disk space on both
SQL Server is configured to use 2 GB of RAM max
The page file is currently at 15GB
After running a number of test on both servers trying different batch sizes etc. the one thing I noticed is that it seems to always error out when SSIS takes over and starts chewing up all the available RAM. This happens after the index is created and SSIS starts "warming caches". On both servers SQL Server uses up about 1.6GB of RAM at this point while SSIS keeps taking over RAM until all physical RAM is used up.
Some questions:
Has anyone been able to process more then 3 million records and if so what is your hardware configuration?
Should we try running SSIS from a different server so it has access to the full amount of physical RAM? (so it doesn't have to fight for RAM with SQL Server)
Should we install Win 2003 Enterprise Server so we can add more RAM?
Any ideas why switching to the page file might be causing errors?
Thanks!!
Keith Doyle
View 17 Replies
View Related
Nov 10, 2014
I have 2 tables with this schema
CREATE TABLE tableValues(
[LASTENCRYPTIONDT] [datetime] NULL,
[ENCRYPTIONID] [int] NULL,
[NAME] [varchar](50) NULL
[Code] ....
I want to update tableToUpdate in batches of 5000 per batch and set the lastenecryptionDT to null based on the the join to the tableValues using the column ENCRYPTIONID, and also output updated rows into another table. Incase I would need to do a rollback.
View 3 Replies
View Related
Mar 24, 2015
i have table below
CREATE TABLE [dbo].[DR_Test](
[source_item_id] [int] NOT NULL,
[source_line_no] [int] NULL,
[buyer_id] [int] NOT NULL,
[seller_member_id] [int] NULL,
[code]...
the table contains more than 80 million records so when i fetch the data using buyer_id & timezone its taking lot of more than 1 hours or so....& where buyer_id is not unique.how to fetch the data fast or need to change the structure of the table
View 3 Replies
View Related
Feb 16, 2006
All,
I need to load a 50 million records table monthly. Any suggestion about the best/fast way to do it?
Thanks a lot
View 2 Replies
View Related
Aug 21, 2007
I have 1+ CSV files (using a foreach loop) which I'm doing a lot of transform work on and then inserting into a SQL database table.
Each CSV file usually contains about 2 days worth of data (contains date stamps) - somewhere in the region of 60k records per day.
The destination table currently contains 3 million+ rows and will get bigger.
I need to make sure that before inserting into the destination table, the data doesn't already exist.
I've read the following article: http://www.sqlis.com/311.aspx
While the lookup method works, it takes ages and eats up memory as it caches the 3m+ records before running for each CSV. Obviously this will only get worse as the table grows in size.
To make things a little more efficient what I'd like to do, is first derive the dates I'm dealing with in the current file - essentially storing the max(date) and min(date) in variables. Then in the lookup SQL use those vars, to reduce the amount of data that needs to be brought into the transformation to check against before inserting into the destination table.
Lookup SQL eg. SELECT * FROM MyTable WHERE Date BETWEEN varMinDate AND varMaxDate.
Ideally I'd use an aggregate transformation and then use the subsequent output from that either in the lookup query or store the output in vars, but I don't think you can do that and I get the feeling I'm approaching this with the wrong mindset.
Any thoughts would be great!
View 6 Replies
View Related
Aug 6, 2015
I have a table that I need to do some computations on all the data but first I need to remove the duplicate records and insert the results into a destination table. Here's the example below. My table has 3.1 million rows. I have tried using the DISTINCT and the GROUP BY but both ways to select the data takes about half a minute to run. I'm wondering if there is a way to increase performance. Users are ok with this time since the process runs overnight but improving it won't hurt. I do have a clustered index on these fields but that doesn't seem to improve any.
SELECTDateYear ,
DateMonth ,
Nbr ,
Nbr1 ,
Nbr2 ,
Datafield1 ,
Datafield2,
[code].....
View 7 Replies
View Related
Mar 12, 2015
We are facing a weird scenario in which the snapshot is getting corrupted after insertupdate few million records in to a table .
SQL Server 2012
windows server 2008 R2
service pack 1
64-bit OS
View 1 Replies
View Related
May 20, 2015
I have a pretty simple SSIS package that fast loads a 100 million record table into a SQL Server 2008 table on a daily basis. This normally runs fine and completes in about 1 hour. As this is perhaps one of our largest running SSIS packages, about once every 2-3 weeks this SSIS will fail/drop connection. Once it fails, the large number of records will start rolling back. This rollback process can take 1+ hours so I cannot even restart the failed SSIS package immediately. This is a problem.
I am looking for a solution or option so I do not have to wait on that rollback to restart this particular, long running SSIS package. Is there an option/setting to leave the partial data set committed and not rollback? Then I could just restart the SSIS package immediately or set it the SSIS to auto-restart 1 time on failure. The first step in the SSIS does a truncate of the destination table.
View 2 Replies
View Related
Aug 17, 2007
Hi,
I have to search the records after the records populated.
I mean to say, i have displayed records in report, if i enter some strings in the textbox and clicked find, then it will highlight the particular records, instead of highlighting the values, is it possible to display only those particular records.
For example, say i have 50 records in a page,i entered some strings in the textbox and clicked find, then it will highlight the particular 5 records one by one which match the criteria i have entered in the texbox, instead of that i have to display only those 5 records.
Please tell me how to implement in this report,
Thanks and Regards
Altaf Nizamuddin
View 4 Replies
View Related
Apr 6, 2005
Hi !!
We are doing a project where we have a table with 800,000 records. We need to implement a search on this 800,000 records. How do we do that?
Assue State = NJ
Criteria: Select County, Select City, Enter Zip Code, First Name and Last Name
This should bring up Information about the person.
How can we implement it efficiently ?
Thanks
View 26 Replies
View Related
Dec 11, 2007
Hello I am trying to create a stored procedure where the users can either type in the last name, or a wild card in order to get the values they are looking for. Sometimes they are not sure how to spell the last name and they will type in the first 2 letters or an * for all to see if they can find it that way. How can I do this??
Here is what I have so far
Code Block
ALTER PROCEDURE [dbo].[AdvSearchRevocations]
(@Enter_LastName nvarchar(25))
AS
SET @Enter_LastName = REPLACE(@Enter_Lastname, '*', '%')
SELECT LastName, FirstName, ReasonOfRevocation, TM#, [I/R #], Date
FROM dbo.Revocations_Tbl
View 8 Replies
View Related
Apr 6, 2004
I have to create a search textbox where if i key in the word "dog",it will search against a field called "Name" in a table.Then it will return all records where the text/data in the "Name" field contains the word "dog".For example,it will return the records where the text/data in the "Name" field has words such as "hotdog","doggie","dog barking","big dog" etc etc.
Can i use simple SQL for this or do i need to configure the full text search service on SQL server 2000 and use the FREETEXT predicate?Thank you in advance for any replies.
View 1 Replies
View Related
Dec 4, 2000
Just like Unique/Distinct command, is these some way I could list just the duplicate records from a table . The field is numeric.
Thanks a lot for you help.
View 3 Replies
View Related
Mar 21, 2006
I'm just learning to join tables and I'm trying to construct a query to tell me the following:
Table names and related columns:
Table1.HdrSys
Table2.HdrSysNum and HdrSys
Table3.HdrSysNum
Table3 contains the detail info for Table2. Table1 is the header table for Table2. So.. If I want all records in Table3 that don't have a related record in Table1, is this even possible?
I started with:
select * from Table3 a
join Table2 b on a.HdrSysNum = b.HdrSysNum
join Table1 c on b.HdrSys= c.HdrSys
-- Now.. how do I qualify the statement.. or can I with just this. Thx! Hope I wasn't too confusing.. because I tend to get that way when I'm confused!
View 4 Replies
View Related
May 4, 2015
having some issues trying to create a query in excel 2013. I can get the data I want from sql, but I get individual transactions and I want to sum them by plu number. here is my query, I tried using group by but every time I add the field, I get an error that some other field is invalid because it's not contained in an aggregate or group by clause. btw, I didn't name these fields.
SELECT RPT_ITM_D.F254 as [Date], RPT_ITM_D.F01 As [PLU], RPT_ITM_D.F64 As [Qty Sold], RPT_ITM_D.F65 As [SOLD], OBJ_TAB.F17 As [RCode], OBJ_TAB.F29 As [Description], PRICE_TAB.F30 As [EL Price], PRICE_TAB.F31 As [Qty]
FROM STORESQL.dbo.OBJ_TAB OBJ_TAB, STORESQL.dbo.PRICE_TAB PRICE_TAB, STORESQL.dbo.RPT_ITM_D RPT_ITM_D
WHERE OBJ_TAB.F01 = RPT_ITM_D.F01 AND OBJ_TAB.F01 = PRICE_TAB.F01 AND PRICE_TAB.F01 = RPT_ITM_D.F01 AND ((RPT_ITM_D.F254>=? And RPT_ITM_D.F254<=? )AND (OBJ_TAB.F17=25))
View 8 Replies
View Related
Aug 2, 2007
Hi all,For now I can use this code to display all the records that begins with a Letter:(WHERE SONG_TITLE LIKE @SONG_TITLE + '%') Now how do I search for records that begins with a number (from 0-9), as an add-on to the above query?Thank you very much,Kenny.
View 6 Replies
View Related
Jun 29, 2004
Hi,
I have a table full of items which can be searched.
I also have another table with the ID of each item and columns for no of times details shown, no of times saved etc.
What I would like to do is increment a value in this second table for each item every time it is returned in a search.
What would be the best way to do this?
(Im using a Stored Procedure)
Thanks in advance,
Pete
View 5 Replies
View Related
Jan 19, 2014
I need to query the contents: Look in Table A of such sets (A, B), which are not present in table B
To illustrate:
Tab A
A....B
aa..kr - this set does not occur in Tab B
bb..gh
vv..kl
cc..er
ss..we
[code]....
View 3 Replies
View Related
Jul 20, 2005
I have a form with a dropdown or combo box, the user can select <All>or pick a user name. If they pick a user name my where clause worksfine, buts what's the best way to write "Select All" if they choosethe <All>This is what I have so far, but I don't think I should be using theLIKE operator.WHERE tblCase.qarep LIKE CASE @myqarep WHEN '<All>' THEN '%' ELSE@myqarep ENDand tblOffice.officecode LIKE CASE @myoffice WHEN -1 THEN '%' ELSE@myoffice ENDthanks for your help!!
View 4 Replies
View Related
