One Table Is Ultra Slow At Being Copied, Others Are Fine
This is a really really odd problem.
Here's the situation. I've got a DB with several tables. Let's pick out two of those tables from the bunch.. they both have approx 2500 rows in them. I take one and duplicate it (right click, All Tasks->Export Data, copy tables, etc) and it duplicates just fine into another DB.
Now, the second table.. takes FOREVER. Yet it has the same amount of rows in it! And this is a flat table export.. I'm not including dependent objects or anything like that.
Not to mention this is slow, but when I use Red-Gate SQL Data Compare, it gets stuck when it hits this specific table.
Anyone have any idea what would cause this?
View Complete Forum Thread with Replies
Related Forum Messages:
Slow Query....drop Index Works Fine!!!!!
We are running MS RS and SQL Server 2000 SP3. We have one LEDGER, where all the daily activities are stored. The LEDGER table has 4 indexes (1 clustered and 3 non-clustered). To get AR we use this table. Well problem is some times in 1-2 months, any simple AR query takes a long time and every other client gets slow response (queries are very slow or sometimes block). If we DROP any index on LEDGER table and again put it back (RECREATE), all our queries work fine and faster. This goes on till 1-2 months, till we see the same issue again. This is a classic case happened today. Queries were running fine till morning 8 AM. We upload some 50 thousand records to Ledger table (Data Conversion). Well after 30 mins, all simple AR queries started taking a long time. We DROPPED an index in LEDGER table and everything was faster....Just to be same we added back the same index again.......everything is Faster..... What is this. ....is it our QUERY, index or huge Transactions or no free space ??? We are scheduled to run SP4, next week. But is there any solution in the mean time on what is this? Also is they any way to KILL all SQL server processes that take more than a mins. We just don't want ALL our client to Slow down because of one query???? Thanks,
View Replies !
Ultra Newbie - Primary Key And STORAGE
I'm trying to learn SQL with SAMS Teach Yourself SQL in 24 hours and using SQL 2000 Server but because the book is a generic SQL code I'm finding some of the examples don't work for me. Firstly is there a STORAGE command in MS SQL as when I tried to create my table and appended: STORAGE (INITIAL 20M NEXT 1M); All I got was an error and couldn't figure how to get round it. Secondly, I created my table via: CREATE TABLE TBL_EMPLOYEE (EMP_IDCHAR(9)NOT NULL, EMP_NAMEVARCHAR(40)NOT NULL, EMP_ST_ADDRVARCHAR(20)NOT NULL, EMP_CITYVARCHAR(15)NOT NULL, EMP_STCHAR(2)NOT NULL, EMP_ZIPINTEGERNOT NULL, EMP_PHONEINTEGERNULL, EMP_PAGERINTEGERNULL); but can't figure out how to now add a Primary Key. I added a line to my QA window: ALTER TABLE TBL_EMPLOYEE ADD CONSTRAINT EMP_ID PRIMARY KEY; And when I parse it, it says it complete's successfully but then when I try and execute it errors. Can you see what I'm doing wrong / should I give up now? Thanks
View Replies !
Havin Trouble Inserting Records To A Table.. Update Works Fine
Hi.. I am getting a xml stream of data and putting it to a object and then calling a big sproc to insert or update data in many tables across my database... But there is one Table that i am having trouble inserting it.. But if i run an update it works fine... This my code for that part of the sproc.. IF Exists( SELECT * FROM PlanEligibility WHERE PlanId = @PlanId ) BEGIN UPDATE PlanEligibility SET LengthOfService = Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS ELSE @rsLengthOfService END, EligibilityAge = CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, EntryDates = @EntryDates, EligiDifferentRequirementsMatch = Case When @PD_EmployeeContribution = 0 Then 0 When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --@CompMatchM, LengthOfServiceMatch = CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END, EligibilityAgeMatch = CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, OtherEmployeeExclusions = @OtherEmployeeExclusions WHERE PlanId = @PlanId END ELSE BEGIN INSERT INTO PlanEligibility ( PlanId, LengthOfService, EligibilityAge, EntryDates, EligiDifferentRequirementsMatch, LengthOfServiceMatch, EligibilityAgeMatch, OtherEmployeeExclusions ) VALUES ( @PlanId, Case When @PD_EmployeeContribution = 0 Then @rsLengthOfServicePS ELSE @rsLengthOfService END,--@rsLengthOfService, CASE When @PD_EmployeeContribution = 0 Then @EligibilityAgePS Else @EligibilityAge End, --@EligibilityAge, @EntryDates, Case When @PD_EmployeeContribution = 0 Then 0 When @PD_EmployeeContribution = 1 and @PD_EmployerContribution = 0 then 0 Else 1 END, --having trouble here CASE When @MCompanyMatch = 0 Then @rsLengthOfServicePs ELSE @rsLengthOfServiceMatch END, CASE When @MCompanyMatch = 0 Then @EligibilityAgePS ELSE @EligibilityAgeMatch END, --EligibilityAgeMatch,@EligibilityAgeMatch, @OtherEmployeeExclusions ) END Any help will be appreciated.. Regards, Karen
View Replies !
Table Visibility Not Functioning Correctly On Server, Works Fine In Visual Studio
I have a report problem. I'm using a parameter to dynamically control visibility for two tables. If the parameter is set to one value, I want to switch one of the tables to invisible, if the parameter is set to another, I want the other table to be invisible instead. This all works fine in Visual Studio. When I publish it to my report server, the visibility controls no longer function and both tables always display. Any ideas here? I'm running 2005, SP2 CTP.
View Replies !
Is It Possible To Prevent Databases From Being Copied?
Hi, We have a point of sale application (C# .NET 2.0) and a Sql Server 2005 database back end. Our customers are concerned that employees could create a backup of the SQL Server database (or even of the MDF file) and use it to steel customer data. Very often, the application is running on a single PC in a shop using Sql Server Express Edition 2005 under Windows XP. The users usually log on as local administrator. It's hard for us to force our customers to change their local security policies. Ideally, I would like some form of security mechanism that prevents a backup from being restored on to another PC without either a password or some other form of authentication. Is this possible? Regards, Sigol.
View Replies !
Attach Db File Copied Previously
I got a mdf file from my co-workerwho just stopped SQL server and delete ldf fileand give me a mdf file.if it was detached, it would work finebut it was just copied.Is there any way to attach this mdf file?I tried sp_attach_db and sp_attach_single_file_dband both failed.thanks,
View Replies !
Different Results Same Query Between Original And Copied Db
Hi all, I restored a backup of a database running SQL Server in W2K to my own laptop (Windows XP) for report testing pourposes. The restore worked perfectly, but when I ran the store procedure that returns my "report" set I noticed that several of the fields within the result set are different, the number of rows and customers are a perfect match to the production report. The fields that are different are calculated fields that invoque a user defined function, which again are exactly the same on both databases. I tried dropping the stored procedure and the 4 functions and recreating them again but I get the same results, the number of rows, the customers and all "non" function calculated fields are perfect, only the fields calculated with the functions are wrong. Has anybody seen this behavior? Thanks for your help Luis Torres
View Replies !
Replication SPs Copied W/CRLF In Names
Hi folks - New DBA (longtime developer) here. I've just noticed something strange on a couple of my databases (SQL 2k). There are some stored procs that have a CRLF or CR+CRLF prefixing the name (they show up as unprintable-char black squares in EM). They all appear to be replication-related SPs (e.g. ). The names are as follows: sp_MSdel_<replicated_table_name> sp_MSins_<replicated_table_name> sp_MSupd_<replicated_table_name> There are corresponding normally-named SPs. Not all the replication-related SPs have been copied this way. The create date of all the new SPs is the same, and is (IIRC, I wasn't involved then...) the date of the SP4 application to the server. Anyone ever seen this? More importantly, is there a way to determine if these SPs are being used? I'd like to delete them if I can, but not until I'm sure of what's happened. TIA for any input.
View Replies !
Char Varchar Not Copied While Transferring
Hi, I have the following problem. I am using DTS connecting SQL SERVER 2005 and Sybase9. I successfully copy from Sybase9 to SQL SERVER 2005. Now when i am copying from SQL SERVER 2005 to Sybase9 char and varchar types are not copied. The data types are the same as well as data type length. Integers, numeric and date values are copied. I have tried data conversion but that did not help. sql server 2005 collation is Greek_CI_AI and Sybase9 collection is 1253ELL Any solution how to solve loosing char and varchar types when copying from SQL SERVER 2005 to Sybase9 ? Note: I connected to Sybase using System DSN Regards, Vasilis
View Replies !
Copied Database Maintains Original Name In Backups
I made a copy of a database "sac_prod" and named the new copy "vgs_prod". Now, when I do a backup of the new database, it still shows the name of the original. Is there any way to change this so it will be the same as the new database name?Here is the BACKUP script:BACKUPdatabase vgs_prod TODISK='\sac-srvr1data$TechnicalSharedProductionSQLBackup LasVegasvgs_prod_CopyOnly.BAK' with COPY_ONLYHere is the messages I received from this BACKUP:Processed 1752 pages for database 'vgs_prod', file 'sac_prod' on file 1.Processed 6 pages for database 'vgs_prod', file 'sac_prod_log' on file 1.BACKUP DATABASE successfully processed 1758 pages in 0.412 seconds (34.955 MB/sec). I would like to change the file 'sac_prod' to be 'vgs_prod' in lines 1 and 2 just above. Thanks,
View Replies !
SSIS Error With Duplicate IDs Of Copied Packages
Hi, I recently encountered an error when I created several copies of one package. It's always nearly the same package with small modifications. I call this packages from a parent package which is part of our datawarehouseing-framework. The problem is, when copying a packages or using a packages as template the packages' IDs and Task's-IDs are the same. And this isn't only an issue concerning logging!! : When the parent package calls one of the copied packages the first task is executed in every package parallely. Furthermore ... when I for example set a breakpoint on a data transformation task in one of the packages, the breakpoint is set in all packages on the same task! This is resulting in strange errors because the tasks-states and variable values seem to get mixed up. Unfortunately there is only a possibility to change the package's ID, but the IDs of tasks are readonly! One solution is, to create a new package and copy all the tasks to the new package which creates new IDs, but doing so, I have to manually recreate a long list of variables, all the configurations, all the connection-managers once again. Furthermore I loose the layout of tasks. I found some posts about it here http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/6f85a31ea190608a/0eae312aa8440cf8?lnk=gst&q=pitfall&rnum=1&hl=de#0eae312aa8440cf8 or http://groups.google.de/group/microsoft.public.sqlserver.dts/browse_thread/thread/760093d58bf6ccb5/32ced2f2020ef3f7?lnk=st&q=data+flow+task+id+copy&rnum=2&hl=de#32ced2f2020ef3f7 saying the issue will be fixed by SP2, but now I don't see any comment on it in the CTP of Service Pack 2. Is there any solution to this problem or official roadmap about a fix from Microsoft?? Greetings Monte
View Replies !
Backup Files In Append Mode To Be Copied.
Hi, I have a database on sqlserver 2005.I usually take a full db backup and sequential transaction log backups and append each of the backups it to a backup file.Now if i want to copy my second transaction log file to a specific folder on my server.Do we have any procedure to do it. Regards Arvind L
View Replies !
Merge Replication Issue With Records Not Being Copied To Subscriber
I have an issue with merge replication between sql2005 sp2 and spl2000 sp4 the merge replication runs fine to 6 other sql servers but the sql2000 sp4 server has a lot of locking going on, but the merge replication doesnt fail it just loses several hundred records without notifying me I have to manually copy the missing records every week or so I have dropped the replication and rebuilt it twice but it still happens
View Replies !
Report Security - Parent Folder Not Copied To Child
Hi Guys, In the reproting services web page I have inserted different users who can run reports. I have home page and sub folders for each company department. only problem is when I add a new user I have to add him/her to all the sub folders one by one (although the user is inserted in the parent folder as well) What am I doing wrong? Cheers Sonny
View Replies !
Identity Field Settings Not Copied By Import Wizard (2005)
It appears that when you use the import/export wizard from within Microsoft SQL Server Managment Studio, the identity attributes of the table being copied are not transferred. For example, say the source table has a column [ref] [int] IDENTITY ( 1 , 1 ) NOT NULL, When the import wizard is done the destination table will have a column named ref, but will not be an identity column. The column definition will be [ref] [int] NOT NULL, instead. Is there a way to change this behavior somewhere in the gui? When doing the import, the only options seems to be 'Enable Identity Insert', but checking this does not affect the definition of the column. -Eric
View Replies !
Extremely Slow Table
Hi,I have a table defined asCREATE TABLE [SH_Data] ([ID] [int] IDENTITY (1, 1) NOT NULL ,[Date] [datetime] NULL ,[Time] [datetime] NULL ,[TroubleshootId] [int] NOT NULL ,[ReasonID] [int] NULL ,[reason_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[maj_reason_id] [int] NULL ,[maj_reason_desc] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[ActionID] [int] NULL ,[action_desc] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[WinningCaseTitle] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[Duration] [int] NULL ,[dm_version] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[ConnectMethod] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[dm_motive] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[HnWhichWlan] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[RouterUsedToConnect] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[OS] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,[WinXpSp2Installed] [nvarchar] (255) COLLATESQL_Latin1_General_CP1_CS_AS NULL ,[Connection] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[Login] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL,[EnteredBy] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_ASNULL ,[Acct_Num] [int] NULL ,[Site] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CS_AS NULL ,CONSTRAINT [PK_SH_Data] PRIMARY KEY CLUSTERED([TroubleshootId]) ON [PRIMARY]) ON [PRIMARY]GOWhich contains 5.6 Million rows and has non clustered indexes on Date,ReasonID, maj_Reason, Connection. Compared to other tables on the sameserver this one is extremely slow. A simple query such as :SELECTSD.reason_desc,SD.Duration,SD.maj_reason_desc,SD.[Connection],SD.aolEnteredByFROM dbo.[Sherlock Data] SDWhere SD.[Date] > Dateadd(Month,-2,Getdate())takes over 2 minutes to run ! I realise the table contains severallarge columns which make the table quite large but unfortunately thiscannot be changed for the moment.How can i assess what is causing the length of Query time ? And whatcould i possibly do to speed this table up ? The database itself isrunning on a dedicated server which has some other databases. None ofwhich have this performance issue.Anyone have any ideas ?
View Replies !
ETL Sort Is Very Slow On A Big Table
Hi, We have developped an ETL. For development we used small test files (10 000 rows) to test if it works correctly. This runs in less then a minute In Test we are using a file which contains all rows (7 million). We did twice a test and we first stopped the process after a week and the 2nd time we stopped the process after a weekend. We are able to trace the problem to the point where it has to sort the tables. The proces is pretty simple. We use two connectors to directly connect to the tables. Then we have two blocks to sort the data. And then we have one block to merge the data. Should we which to let SQL do the sorting ? Since it is in staging is has no index on that column. A select on the tables with an order by takes 3 minutes to return all those rows. Any idea's ? Also is there a page with the best practices for ETL ? Constantijn
View Replies !
Slow INSERTs On A Table
Hello all. I've got a problem with really slow INSERTs on one (and only one) of the tables in a database. For example, using SQL Management Studio, it takes 4 minutes and 48 seconds to insert 25 rows. There are only about 8 columns in the table and only about 1500 records. All the other tables in the database are very fast for inserts. Another odd thing uniquely associated with INSERTs on this table: prior to inserting the 25 new rows of data, SQL Management Studio tells me that it inserted 463 rows of data which I know did not happen. Here's the INSERT statement: INSERT INTO FieldOps(StudySiteID , QA_StructureID , Notes , PersonID) SELECT DISTINCT StudySiteKey , QA_StructureKey , SampleComments1 , '25' FROM ScriptOutput_Nitrate WHERE (ScriptOutput_Nitrate.StudySiteKey IS NOT NULL) and SQL Management Studio (eventually) says: (463 row(s) affected) (463 row(s) affected) (25 row(s) affected) The table has an index on the primary key (INT data type with auto increment). I tried running the following code to fix things but it made no difference: USE [master] GO ALTER DATABASE [FieldData] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO use FieldData GO DBCC CHECKTABLE ('FieldOps', REPAIR_REBUILD) With ALL_ERRORMSGS GO USE [master] GO ALTER DATABASE [FieldData] SET MULTI_USER WITH ROLLBACK IMMEDIATE GO I'm guessing that the problem might be related to the index (??). I don't know... Does anyone here have a suggestion as to what I should do to fix this problem.
View Replies !
Very Small Table Incredibly Slow
Hello.I am administering a SQL Server (Enterprise Edition on Windows 2003)from some month and can't understand what is going on in the latestweek (when the db grow a lot).The DB is around 250G, and has one table with 1 billion rows. It isperforming in a decent way, but can't understand why a particolar tablehas strong performance problem.I have a stored procedure that read table from table A and insert them,after processing in table B, and then move them in other table (similarto a Star Schema) for reporting.Table B is, for how the SP is written, not more than 3000 lines. TableB is very simple, has 3 rows, and no index.What is very strange is that performance of table B is really slow. IfI do a select count (*) from table_b it takes between 30s & 2minutes toreturn it has 0 lines. When the stored procedure insert 1000 lines, ittakes 20/30 seconds and it takes 20/30 seconds to delete them.To me it doesn't look like a lock problem, because it is slow also whenthe only procedure that access that table are stopped. I did an updatestatistics with fullscan on this table with no improvement.The DB is on a Storage Area Network that should perform decently. TheLUN I use is configured to use a piece of 32 disk that are used also byother application. I don't have performance data of the SAN. Themachine is an HP DL580 with 4 CPU (hiperthreading disabled), 8G of RAM,AWE and PAE and 5G reserved for SQL Server.I don't know what to do to solve this situation. Could it be a"corruption problem" that slow this table so much? is it possible thefact the db grow a lot in the last week created problem also to thissmall and simple table?Do you have any idea or hint on how to manage this situation, orpointer to documentation that can help in analizing this situation?For the ones that arrived till here, thank you for your time andpatience reading my bad english...Best Regards,MamoPSI can't rewrite the stored procedure, because it is part of a closedsource product.
View Replies !
Select Top 100 Query Very Slow One Table
Here is the table in question.Number of records 5512825not a big table and plenty of good indexs i think? that is the bigquestions.Could some one help?After the table creation and index you will see a query that takesalmost 2.3 minutes to run this is totally bad. That time is throughthe query analyzer.Any help here would be greatly apperciated.CREATE TABLE [dbo].[tblpolmaster] ([IDX] [int] IDENTITY (1, 1) NOT NULL ,[AG_NO] [int] NOT NULL ,[PR_NO] [int] NOT NULL ,[PLAN_NO] [char] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[SUB_PLAN_NOS] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[FIN_NO] [int] NOT NULL ,[ACCT_NO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[DRAFT_NO] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_LNAME] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_FNAME] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_MI] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_ADDRESS] [char] (35) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_CITY] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_STATE] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_ZIP] [char] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_PLUS4] [char] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_PHONE] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_FAX] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_MEMBER] [char] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[POL_NUM] [char] (22) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[USER_ID] [char] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[PR_COUNT] [int] NOT NULL ,[TRANS_DT] [smalldatetime] NULL ,[PR_CERT] [int] NOT NULL ,[EDIT_DT] [smalldatetime] NULL ,[POL_NUM_MEMBER] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_ASNULL) ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPLAN_NO] ON[dbo].[tblpolmaster]([PLAN_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_NAME] ON[dbo].[tblpolmaster]([POL_LNAME], [POL_FNAME]) WITH FILLFACTOR = 90ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_STATE] ON[dbo].[tblpolmaster]([POL_STATE]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_NUM] ON[dbo].[tblpolmaster]([POL_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterAG_NO] ON [dbo].[tblpolmaster]([AG_NO])WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPR_NO] ON [dbo].[tblpolmaster]([PR_NO])WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_ADDRESS] ON[dbo].[tblpolmaster]([POL_ADDRESS]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_PHONE] ON[dbo].[tblpolmaster]([POL_PHONE]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPR_COUNT] ON[dbo].[tblpolmaster]([PR_COUNT]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterFIN_NO] ON[dbo].[tblpolmaster]([FIN_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterUSER_ID] ON[dbo].[tblpolmaster]([USER_ID]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterTRANS_DT] ON[dbo].[tblpolmaster]([TRANS_DT]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_MEMBER] ON[dbo].[tblpolmaster]([POL_MEMBER]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterDRAFT_NO] ON[dbo].[tblpolmaster]([DRAFT_NO]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterEdited] ON[dbo].[tblpolmaster]([EDIT_DT]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_NUM_MEMBER] ON[dbo].[tblpolmaster]([POL_NUM_MEMBER]) WITH FILLFACTOR = 90 ON[PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_FNAME] ON[dbo].[tblpolmaster]([POL_FNAME]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_LNAME] ON[dbo].[tblpolmaster]([POL_LNAME]) WITH FILLFACTOR = 90 ON [PRIMARY]GOCREATE INDEX [IX_tblpolmasterPOL_CITY] ON[dbo].[tblpolmaster]([POL_CITY]) WITH FILLFACTOR = 90 ON [PRIMARY]GOSelect Top 100 tblpolmaster.Idx,tblpolmaster.Ag_No, tblpolmaster.Pr_No, tblpolmaster.Pol_LName,tblpolmaster.Pol_FName,tblpolmaster.pol_Address, tblpolmaster.Pol_City,tblpolmaster.Pol_State, tblpolmaster.POL_MEMBER, tblpolmaster.Pr_CountFROM tblpolmaster WITH (NOLOCK)Wheretblpolmaster.PR_NO = 514And tblpolmaster.POL_STATE = 'FL'
View Replies !
Slow SELECT On Single Table
SELECT * on a 4000 row table is taking more than 12 seconds. Other larger tables are not nearly as slow. I've DBCC dbreindex'd, and dbcc showcontig shows density at 100%. How can I figure out why this is happening? What are some remedies? Thanks for your help.
View Replies !
Table Access Has Become Very Slow After DB Restore
I have a table that has appx 3.2 million rows. see sp_help Name Owner Type Created_datetime ------------------------------------------------------------------- TB_SAAI014_BPD dbo user table 2005-08-10 11:33:23.893 Column_name Type Comp Lngth Prec Scale Nullable ------------------------------------------------------------------------ RowID int no 4 10 0 no SPHInstID int no 4 10 0 no BPDInstID int no 4 10 0 no BMUID varchar no 11 no InfoImblCfw numeric no 9 12 2 no BMUPrdNonDel numeric no 9 12 2 no PrdFPN numeric no 9 13 3 no PrdBMUBalSrvVol numeric no 9 13 3 no PrdInfoImblVol numeric no 9 13 3 no PrdExpdMtrVol numeric no 9 13 3 no BMUMtrVol numeric no 9 13 3 no PrdBMUNonDelBidVol numeric no 9 13 3 no PrdBMUNonDelOfrVol numeric no 9 13 3 no TranLossFctr numeric no 9 15 7 no TranLossMtpl numeric no 9 15 7 no TradUnitName varchar no 30 no TotTrdUnitMtrVol numeric no 9 13 3 no BMUAppBalSrvVol numeric no 9 13 3 no DTCreated datetime no 8 yes DTUpdated datetime no 8 yes Identity Seed Inc Not Repl ----------------------------------------- RowID 0 1 0 RowGUIDcol ----------------------------- No rowguidcol column defined. Data Located on File Group ========================== PRIMARY Index Name Decsription Keys ---------------------------------------------------------------------------- idx_SPH_BPD clustered, unique located on PRIMARY SPHInstID, BPDInstID This table has 1 clustered index based on its own unique record ID and that of its parent table record I have an import process that adds appx 980 rows of data to this table and numerous rows to several other tables as part of a transaction and it ran in about 15 seconds. However we suffered a server failure and it had to be rebuilt (Svr2k3), SQL 2000 re-installed (with default options) and the data base restored. The same transaction is now taking 8 to 9 minutes. I tracked it down to this particular table. Just doing a count(*) takes over 5 minutes. Select * where ID = 1 takes over 5 mins. Also, whenever the table is accessed you can hear the server thrashing the disks. Other tables, although smaller do not seem to be suffering from this masive performance drop.. I've tried droping and recreating the index. I have even created a copy of the table, with index, and still get the same issue with speed. DBCC CHECKTABLE returns the following but takes 6 and a half minutes DBCC results for 'TB_SAAI014_BPD'. There are 3168460 rows in 72011 pages for object 'TB_SAAI014_BPD'. DBCC execution completed. If DBCC printed error messages, contact your system administrator. No errors are shown A DBCC CHECKTABLE on another table with 230 thousand rows, run at the same time only took 10 seconds Can anyone please point me in the direction of things to check, try or repair. Any help greatfully recieved. Jinx1966
View Replies !
Slow Query With Table Containing Image
Hi, I have a table defined as such: PosterArtId int no 4 10 0 no (n/a) (n/a) NULL Graphic image no 16 yes (n/a) (n/a) NULL GraphicFilename varchar no 50 no no no SQL_Latin1_General_CP1_CI_AS I have a Stored Procedure defined in the database that queries this table (joins with some other tables) that takes about 1 1/2 minutes to return results (running directly in query analyzer). The table itself has 8900 records and the resulting stored procedure returns 33 rows. I have backed up this database and restored it to another database on a different machine also running SQL Server 2000. When I run the same stored procedure on this 2nd database (note the contents of the database and this table are exactly the same), it runs very quickly - in about 2 seconds. I'm trying to figure out what is causing the query to run so slow on the original database, which is our production database server (note that none of the other queries seem to be running extra slow on this machine, just this particular one). I've since been reading up on storing images in the database and I don't think the images are stored "text in row" - I ran the command: €œSELECT OBJECTPROPERTY(OBJECT_ID('tblPosterArt'),'TableTextInRowLimit')€? and got a 0 return value. My ultimate goal is to figure out: 1) why the timing is so different on the two databases even though the have the same data 2) is there something we can do to speed up the results on our production server For the first goal, I'm heading down the path that something in the database backup/restore did not recreate the btree storage of the image data in the same manner. Would this be correct? If not, is there some kind of analysis that I can do that will tell me some useful information? I've run the Stored Procedure for both databases in query analyzer with the "show execution plan", "trace", and "statistics" turned on. In the Execution Plan of the production database I see a significant amount of time in three areas: Nested Loops/Left Semi Join, Clustered Index Scan, and Clustered Index Seek. But being as I'm not a dba (nor do we have one on staff), I'm not sure how to interpret this data. I keep wanting to point to some sort of environment issue since the data is the same between the two machines. I suppose there is nothing to do about the 2nd goal without knowing why the query on the one machine is taking so long. Any thoughts on how to get more information here? Thanks, Beth
View Replies !
Table Variable With Update Slow
Hi I have an update query that joins with 2 table variables. this update updates about 50,000 rows in a table. update table1 from @table2 t2 inner join @table3 t3 on t2.id = t3.id where table1.id = t2.id the problem is that sometimes this update takes forever. If I replace the table variable with regular tables, it only takes about 11 seconds. I am not sure what the problem is. Could this be a problem with the tempdb. What should I be looking for in the tempdb that might cause a problem.
View Replies !
Large Table, Really Slow Queries
I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one. The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more: select count(dt_date) as Searches from SearchRecords where datediff(day,getdate(),dt_date)=0 As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).
View Replies !
Very Slow Query (select Count(*) From Table)
Dear MS SQL Experts,I have to get the number of datasets within several tables in my MSSQL2000 SP4 database.Beyond these tables is one table with about 13 million entries.If I perform a "select count(*) from table" it takes about 1-2 min toperform that task.Since I know other databases like MySQL which take less than 1 sec forthe same taskI'm wondering whether I have a bug in my software or whether there areother mechanisms to get the number of datasets for tables or the numberof datasets within the whole database.Can you give me some hints ?Best regards,Daniel Wetzler
View Replies !
DTS Transfer Table With Text Column Slow
I am using DTS to transfer some tables from one server to another as part of a migration. We want to be down for as little time as possible, but we need the most up-to-date copy of the database tables in question. I am currently testing the transfer process in our test environment by migrating the data from one database to another on the same SQL instance. There are 7 tables to transfer and the total size of the database is 450 MB (with around 117 MB used). The two largest tables have around 17,000 records each. One table (the header) has no text column and it takes just a few seconds to transfer. The other table (the detail) has two columns, one of which is a text column (actually, its not fair to call it the detail table; the relationship is actually one-to-one, but for the sake of this discussion, let's leave it at that). The header takes seconds to transfer, but the detail takes up to 18 minutes. Physically, our test server is quite robust; 2 processors, a 3 disk RAID-5 for the data files and a separate RAID 1 partition for the logs. Performance counters don't indicate any real issues: during the transfer, the disk utilization on the data partition occasionally spikes to a high level, but comes right back down until the next spike (the spikes being separated by about 1 minute. No issues with memory, paging or CPU. I have removed the clustered index on the affected table as well as the PK. No help. Are text columns just slow? Is there something that I am missing? Regards, hmscott
View Replies !
Why Is SQL Server 7 So Slow? I Only Have About 11500 Rows In The Table
I wonder how SQL Server 7 can be so slow!! I use an external application (that we made) which reads information from SQL Server 7 databases. What I mean is, one of my allications reads information about "users" and there is about 11500 rows in the table with 34 columns in each. My application shows information about one person at a time. And then there's a scrollbox at the bottom where one can scroll to see other users (about 11500 different users). When I'm using the scrollbars to move down and get information about other users, I see that the CPU-usage is 100% all the time. And the "hour-glass" (windows thingy to show that there's a delay) is shown for maybe a second. And at first it takes almost 10 seconds to (I suppose?) read in the information from the table into my application and show information about the first user. I don't think it should take almost a second or so to just scroll this list of users. How can SQL Server be so slow? I have 196 megabyte ram on this computer that I use for development. All databases together are less than 15 megabytes. Less than 10 megabytes I suppose. I mean, in Access97 it was much faster but whas gettins slower for certain occurances with much more data int he tables, and the whole idea of converting the system to SQL Server7 was to got fast responsetimes. Just to clarify, the client-application and the SQL Server resides on the same computer right now so they don't have to go over some kind of network-conenction etc. What is the big problem here? regards, Bob Nachbar.
View Replies !
Opening Table In Management Studio Is Very Slow
Dear Gurus When I open a large table (say more than 1,000,000 Rows) in the SSMS by right clicking on the table name, it takes a very big time to fully open the table.More than 20 minutes for 1,000,000 records on a local instance. SQL Server 2000 EM was extremely faster. Does any one knows a work around? I need to be able to view and edit the data in SSMS. Thanks in advance. Parviz
View Replies !
SLOW Performance On Table With Image Fields (SQL 2000)
HiWe have a SQL server 2000 SP4 on a windows 2003 2x3Ghz XEON 4 GB ram.We have a table looking like this with currently 6 rows. Total data is aprox10 kb i all row all together.CREATE TABLE [dbo].[BIOMETRICPROFILE] ([BIOMETRICPROFILEID] [bigint] IDENTITY (1, 1) NOT NULL ,[FINGERPRINTTEMPLATE1] [image] NOT NULL ,[FINGERPRINTTEMPLATE2] [image] NOT NULL ,[FINGERPRINTTEMPLATE3] [image] NOT NULL ,[FINGERPRINTTEMPLATE4] [image] NOT NULL ,[FINGERPRINTTEMPLATE5] [image] NOT NULL ,[FINGERPRINTTEMPLATE6] [image] NOT NULL ,[TYPE] [nvarchar] (50) COLLATE Danish_Norwegian_CI_AS NOT NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOselect * from BIOMETRICPROFILE takes ~4 seconds (!) to execute thourgh Queryanalyzer. Alle other tables has no performance problems.We have a SQL 2005 express instalation on the same server. If we restore abackup from the sql 2000 database the query takes aprox ~ 15 ms.What isgoing on here?Has SQL 2000 problems with image fields? or how can we find the problem?RegardsAnders
View Replies !
Large Table/slow Query/ Can Performance Be Improved?
I am having performance issues on a SQL query in Access. My query isaccessing and joining several tables (one very large one). The tables arelinked ODBC. The client submits the query to the server, separated byseveral states. It appears the query is retrieving gigs of data from thetable and processing the joins on the client. Is there away to perform moreof the work on the server there by minimizing the amount of extraneous tabledata moving across the network and improving performance (woefully slowabout 6 hours)?
View Replies !
Slow Performance With A Simple Query In A Small Table?
In my database/MY SERVER (SQL7/Win2K), I run a simple query with a Table/10000 rows (without cluster index): SELECT * FROM TABLE it take over 30s. Why it's slow? How can I check for reason? How to configure my server to improve performance? Thanks in advance. TH ---------------------------------- SP_CONFIGURE's RESULT in MY SERVER ---------------------------------- Table 'spt_values'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0. name minimum maximum config_value run_value ----------------------------------- ----------- ----------- ------------ ----------- affinity mask 0 2147483647 0 0 allow updates 0 1 1 1 cost threshold for parallelism 0 32767 5 5 cursor threshold -1 2147483647 -1 -1 default language 0 9999 0 0 default sortorder id 0 255 52 52 extended memory size (MB) 0 2147483647 0 0 fill factor (%) 0 100 0 0 index create memory (KB) 704 1600000 0 0 language in cache 3 100 3 3 language neutral full-text 0 1 0 0 lightweight pooling 0 1 0 0 locks 5000 2147483647 0 0 max async IO 1 255 32 32 max degree of parallelism 0 32 0 0 max server memory (MB) 4 2147483647 2147483647 2147483647 max text repl size (B) 0 2147483647 65536 65536 max worker threads 10 1024 255 255 media retention 0 365 0 0 min memory per query (KB) 512 2147483647 1024 1024 min server memory (MB) 0 2147483647 0 0 nested triggers 0 1 1 1 network packet size (B) 512 65535 4096 4096 open objects 0 2147483647 0 0 priority boost 0 1 1 1 query governor cost limit 0 2147483647 0 0 query wait (s) -1 2147483647 -1 -1 recovery interval (min) 0 32767 0 0 remote access 0 1 1 1 remote login timeout (s) 0 2147483647 5 5 remote proc trans 0 1 0 0 remote query timeout (s) 0 2147483647 0 0 resource timeout (s) 5 2147483647 10 10 scan for startup procs 0 1 0 0 set working set size 0 1 0 0 show advanced options 0 1 1 1 spin counter 1 2147483647 10000 10000 time slice (ms) 50 1000 100 100 two digit year cutoff 1753 9999 2049 2049 Unicode comparison style 0 2147483647 196609 196609 Unicode locale id 0 2147483647 1033 1033 user connections 0 32767 0 0 user options 0 4095 0 0 Table 'spt_values'. Scan count 43, logical reads 108, physical reads 0, read-ahead reads 0. Table 'sysconfigures'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 2.
View Replies !
Help With Slow Embedded Sql Calls To Build A Dynamic Html Table
Ok, here is the scope of my current project. I need to build a small / simple scheduling application to show people's availability. I thought I had it all worked out, and then I started loading a bunch of data in it and it craws.The table holds the user, the event date and the event typeI have loop that builds an HTML table with the number of days in the month that you want to view. Basically just building <td></td> tags for each day of the month.Then I query the users that should be on the schedule. Then during that loop, I have a second loop to get each event that might happen for each user for each day of the month. Any help on a better more efficient way of doing this would be gratefully appreciated. (be kind, I am a newbie) Thanks, Jamie public partial class resourceManagementCalendar : System.Web.UI.Page{ int myDaysInMonth; string myDayOfWeek; string myDayOfWeekTrimmed; string myconnectionString; protected void Page_Load(object sender, EventArgs e) { //Get Connection Information from Web.Config ConnectionStringSettingsCollection connections = ConfigurationManager.ConnectionStrings; myconnectionString = connections["myDBConnectionString"].ConnectionString; } protected void ddMonths_SelectedIndexChanged(object sender, EventArgs e) { System.Globalization.Calendar gbCal = CultureInfo.InvariantCulture.Calendar; int selectedMonth = Convert.ToInt32(ddMonths.SelectedValue); myDaysInMonth = gbCal.GetDaysInMonth(2007, selectedMonth, 1); lblCalendarDisplay.Text = ""; int myMonth = Convert.ToInt32(myDaysInMonth); //build the day number of the day of week lblCalendarDisplay.Text = "<tr>"; lblCalendarDisplay.Text += "<td>" + ddMonths.SelectedItem.Text + "</td>"; for (int counter = 1; counter <= myMonth; counter++) { lblCalendarDisplay.Text += "<td style="width: 15px">" + counter.ToString() + "</td>" ; } lblCalendarDisplay.Text += "</tr>"; //build the day name of the day of the week lblCalendarDisplay.Text += "<tr>"; lblCalendarDisplay.Text += "<td> </td>"; for (int counter = 1; counter <= myMonth; counter++) { DateTime myDate = Convert.ToDateTime(ddMonths.SelectedItem.Text +"/"+ counter+ "/2007"); myDayOfWeek = gbCal.GetDayOfWeek(myDate).ToString(); myDayOfWeekTrimmed = myDayOfWeek.Remove(1, myDayOfWeek.Length - 1); lblCalendarDisplay.Text += "<td>" + myDayOfWeekTrimmed.ToString() + "</td>"; } lblCalendarDisplay.Text += "</tr>"; //Display the resource and if they have something assigned. //lblCalendarDisplay.Text += "<tr><td>-</td>"; SqlConnection objConn = new SqlConnection(myconnectionString); string sSql; sSql = "SELECT userID, lastName +', '+ firstName AS resourceName FROM a.dbo.peopleRolesView WHERE (role = 'Resource') ORDER BY lastName"; SqlCommand myCMD = new SqlCommand(sSql, objConn); objConn.Open(); SqlConnection objConn2 = new SqlConnection(myconnectionString); objConn2.Open(); SqlDataReader myReader = myCMD.ExecuteReader(); if (myReader.HasRows) while (myReader.Read()) { //resourceName lblCalendarDisplay.Text += "<tr><td>" + myReader.GetValue(1).ToString() + "</td>"; //build data for resouce for (int counter = 1; counter <= myMonth; counter++) { string sSql2; sSql2 = "SELECT userID, eventType, comments, eventDate, eventID FROM EventsView where userID =" + myReader.GetValue(0).ToString() + " AND eventDate = '" + ddMonths.SelectedValue + "/" + counter + "/2007'"; SqlCommand myCMD2 = new SqlCommand(sSql2, objConn2); SqlDataReader myReader2 = myCMD2.ExecuteReader(); if (myReader2.HasRows) while (myReader2.Read()) { lblCalendarDisplay.Text += "<td> <img src="Images/" + myReader2.GetValue(1).ToString() + ".gif" alt="" + myReader2.GetValue(2).ToString() + "" /></td>"; } else { lblCalendarDisplay.Text += "<td> </td>"; } myReader2.Close(); } lblCalendarDisplay.Text += "</tr>"; } else Console.WriteLine("No rows returned."); myReader.Close(); objConn2.Close(); objConn.Close(); } }
View Replies !
[MSSQL] Blob-upload In Table With Full Text Index Goes Slow
i have a MSSQL 2000 database with about 30 tables in it. On one of those tables i've defined an full text index on an image field. In this table are around 500 records with binary files. it functioned well for a time but now when i try to upload a file into the table this goes extremely slow (300 KB takes over 3 minutes). i tried disabling "change tracking" but this didn't help a thing adding blobs to other tables (without fulltext index on it) still goes fast. what could be a reason that the uploading goes so slow??
View Replies !
SQLEXPRESS Backup File Losing &&"NETWORK SERVICE&&" User When Copied
I'm using the methods of the Microsoft.SqlServer.Management.Smo namespace within a .NET application to create a backup file from a SQLEXPRESS database. I can then restore the database from that backup device using methods in the same namespace. Here is a snippet from the restore code: srv = New Server("MYPCSQLEXPRESS") db = srv.Databases("washmaster") Dim bdi As New BackupDeviceItem(BackupFileName, DeviceType.File) Dim recoverymod As RecoveryModel recoverymod = db.DatabaseOptions.RecoveryModel rs.NoRecovery = False rs.Devices.Add(bdi) rs.Database = "washmaster" rs.ReplaceDatabase = True srv.KillAllProcesses("washmaster") rs.SqlRestore(srv) This works great as long as I used one of the backup files that I created directly on the disk. However, my application has a utility that allows the user to copy the backup files onto another drive, such as a CD or a thumb drive and when I try to restore from the copy of the backup, I get the following exception: ....Cannot open backup device..[filename]...Operating system error 5(Access is denied.) The reason I get this error is that the "NETWORK SERVICE" account was removed from the file permissions when the file was copied. How can I copy a backup to another drive and preserve the "NETWORK SERVICE" account? If I can't do that, is it wise to try to add the account back to the file before using it to restore or is there a better way? Thanks, SJonesy
View Replies !
Before It Was Working Fine..but Now...
Hi, I am having 'INSERT STATEMENT CONFLICTED' error since I try to configure sql server 2005 Full Text Search for my database. Before everything was going fine but after doing some changings I mess up my all DB's. None of my database is working. The error only occur while inserting the records using web form. But I can insert directly from Management Studio interface. here is part of my error: ERROR: System.Data.SqlClient.SqlException: The INSERT statement conflicted with the FOREIGN KEY constraint "FK_Dept_LegalLaw_Dept_LegalMinistries1". The conflict occurred in database "LegalDB", table "dbo.Dept_LegalMinistries", column 'RegID'. The statement has been terminated. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() ...............AND SO ON INSERT QUERY: INSERT INTO Dept_LegalLaw ( Agreement,Amendment,Attachment,Html,ID,IssueNo,LawDate,LawID,LawNo,Mistakes,Name,NameSearch,Pages,RegID,Scanned,SerialNo,Path) VALUES ( '1',NULL,NULL,'1','C0667','2666','5/26/2004 12:00:00 AM','2','22',NULL,N'تعميم بشأن عطلة ذكرى المولد النبوي الشريÙ?',N'مرسوم اميري رقم 29 لسنة 2001 بانشاء لجنة الاسكان والاعمار','1','RAC,',NULL,'5556','RCAB4505.htm') --RETURN THE NEW IDENTITY VALUE SELECT SCOPE_IDENTITY() MediaID I really need urgent help. that how i can reverse this thing back. Thanks
View Replies !
How To Fine A Hole In A Records?
Hi all!I need your help to realize algorithm for stored proc or trigger.tool: MS SQL server 2000, T-SQLTABLE:[unique_id] [mynumber] [week][unique_id] - bigint,primary key, identity auto-increnment[week] - int, 1-53, week number[mynumber] - int, 1 - 7, for every week, daily record one per day, upto 7 per weekso, for every week we have a mynumber from 1 to 7or nothing (if no records for that day),we can insert or delete mynubers in any order, at willEXAMPLE:week 1, mynumber 1,2,3 - so if we insert a new record, mynumber value= 4week 2, mynumber 1,2,3,5,7 - so next mynumber = 4QUESTION:How to use _only_ T-SQL find a missed numbers for particular week whenI'm insert a records?Thanks.Chapai
View Replies !
Fine Tuning This Sql-query. Help!
Hi, I have problem running this query. It will time out for me...My database are small just about 200 members.I have a site for swaping appartments (rental). my query should lookfor matchin a triangle. Like this member A -> B->CA give his appartment to B. B gives his appartment to C and finallyC gives his appartment to ASoo my query looks for matching parameters like rooms, location, sizeandsoo on..I have one table for existing appartments and one for "whantedappartments"and 1 table called "intresse" where members can store "yes" or "no" ifthey are interessted in a appartment.I also have a table called "omrade" to store locations of interest.Hope you can helpe me with some tip soo i can run this query in a fewseconds instead of 20-30 secThanks MSELECTF.medlemsNr as medlemsNr, F.lfId AS lfId, F.ort AS ort, F.gatuadressAS gatuadress, F.gatuNr AS gatuNr, F.rum AS rum,F.storlek ASstorlek,F.hyra AS hyra, count(F.medlemsNr) As hitsFROMmedlem08 A, medlem08 B, medlem08 C, lagenhetF08 D,lagenhetO08 E, lagenhetF08 F, lagenhetO08 G, lagenhetF08 H,lagenhetO08 IWHERED.rum >= I.rumMin AND D.rum <= I.rumMax ANDD.storlek >= I.storlekMin AND D.storlek <= I.storlekMax ANDI.hyraMax = 0" & " OR D.hyra <= I.hyraMax) ANDI.balkong = '" & "" & "' OR D.balkong = I.balkong) AND(I.badkar = '" & "" & "' OR D.badkar = I.badkar) AND(I.bredband = '" & "" & "' OR D.bredband = I.bredband) AND(I.hiss = '" & "" & "' OR D.hiss = I.hiss) AND(I.spis = '" & "" & "' OR D.spis = I.spis) AND(I.brf = '" & "" & "' OR D.brf = I.brf) ANDD.postNr IN (select postNr from ONSKEMAL08 where loId=I.loId) ANDF.medlemsNr Not IN (select medlemsNr2 from INTRESSE08 wheremedlemsNr1=A.medlemsNr) ANDH.rum >= G.rumMin AND H.rum <= G.rumMax ANDH.storlek >= G.storlekMin AND H.storlek <= G.storlekMax AND(G.hyraMax = 0" & " OR H.hyra <= G.hyraMax) AND(G.balkong = '" & "" & "' OR H.balkong = G.balkong) AND(G.badkar = '" & "" & "' OR H.badkar = G.badkar) AND(G.bredband = '" & "" & "' OR H.bredband = G.bredband) AND(G.spis = '" & "" & "' OR H.spis = G.spis) AND(G.brf = '" & "" & "' OR H.brf = G.brf) ANDH.postNr IN (select postNr from ONSKEMAL08 where loId=G.loId) ANDF.rum >= E.rumMin AND F.rum <= E.rumMax ANDF.storlek >= E.storlekMin AND F.storlek <= E.storlekMax AND(E.hyraMax = 0" & " OR F.hyra <= E.hyraMax) AND(E.balkong = '" & "" & "' OR F.balkong = E.balkong) AND(E.badkar = '" & "" & "' OR F.badkar = E.badkar) AND(E.bredband = '" & "" & "' OR F.bredband = E.bredband) AND(E.hiss = '" & "" & "' OR F.hiss = E.hiss) AND(E.spis = '" & "" & "' OR F.spis = E.spis) AND(E.brf = '" & "" & "' OR F.brf = E.brf) ANDF.postNr IN (select postNr from ONSKEMAL08 where loId=E.loId) ANDA.medlemsNr=D.medlemsNr AND A.medlemsNr=E.medlemsNr ANDB.medlemsNr<>A.medlemsNr AND C.medlemsNr<>A.medlemsNr ANDB.medlemsNr<>C.medlemsNr ANDB.sparr<>1 AND C.sparr<>1 ANDA.typ=11 AND A.medlemsNr=" & session("medlemsNr") & " ANDB.medlemsNr=F.medlemsNr AND B.medlemsNr=G.medlemsNr ANDB.typ=11 AND A.triangel=1 AND B.triangel=1 AND C.triangel=1 AND " &_C.medlemsNr=H.medlemsNr AND C.medlemsNr=I.medlemsNr ANDC.typ=11 group by F.lfId, F.medlemsNr,F.ort,F.gatuadress,F.gatuNr,F.rum,F.storlek,F.hyra
View Replies !
1st 2 Upgrades To 7.0 Worked Fine; 3rd One Did Not!
My upgrades to 7.0 from 6.5 worked flawlessly on my dev. and test servers. Needless to say, my prod. box upgrade is not working at all. My install gets to the point where it is starting my 7.0 server and there it fails. The cnfgsrv.out files has this error: driver={sql server};server=ROPEER;UID=sa;PWD=;database=master [Microsoft][ODBC SQL Server Driver][Named Pipes]Specified SQL server not found. [Microsoft][ODBC SQL Server Driver][Named Pipes]ConnectionOpen (CreateFile()). SQL Server configuration failed. After that, it must delete most of the files from my Mssql7 directory because the only thing left there is the install directory. (i.e the errorlog is gone) Thanks in advance for any words of wisdom anyone may have. Palmer
View Replies !
Sql Job Fails But When Run Outside Works Fine
Hi.. I am stuck at a very awkward place. I have created one package which uses an oracle view as its source for data transfer the problem is when i run the package through dtexec it works fine but when i try to schedule it I get the following error Error: 2008-03-24 13:52:40.22 Code: 0xC0202009 Source: pk_BMR_FEED_oracle Connection manager "Conn_BMR" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft OLE DB Provider for Oracle" Hresult: 0x80004005 Description: "Oracle client and networking components were not found. These components are supplied by Oracle Corporation and are part of the Oracle Version 7.3.3 or later client software installation. Provider is unable to function until these components are installed.". I am able to run the package outside the sql job and also connect to the oracle. I have oracle 9i client installed on the server and sql server is 2005. Any help would really be appreciated..
View Replies !
Fine Tuning Results
Hi, I am running the query below but I am getting the following results:- Store Prod_no Quant_Sold Total Orders Total_Sale 115 M1015 4 4 4000 115 M1015 2 1 2000 For some reason on some of the products there is more than one entry for that day per store. As you can see above there is two entries for the product M1015. I need the results to look like the below: Store Prod_no Quant_Sold Total Orders Total_Sale 115 M1015 6 5 6000 SELECT store.branch_no AS store_no, crea_date, jobstock.prod_no, ((jobstock.quantity)*COUNT(jobstock.job_id)) AS quantity_sold, COUNT(jobstock.job_id) AS total_orders, SUM(jobstock.total*100) AS store_total, cast (SUM(jobstock.total/1.25)*100 AS INTEGER) AS store_total_before_tax, ((((stock.curr_cost*jobstock.quantity)*100)*COUNT(jobstock.job_id))) AS cost FROM store, pickup, jobstock, stock WHERE (store.store_id=pickup.store_id) AND (pickup.job_id=jobstock.job_id) AND (stock.prod_no=jobstock.prod_no) AND (stock.store_id=pickup.store_id) AND branch_no>0 and crea_date between '2008-02-25' and '2008-02-28' GROUP BY store.branch_no, jobstock.prod_no, jobstock.quantity, stock.curr_cost, crea_date ORDER BY crea_date Any Ideas would be much appreciated. thanks you.
View Replies !
Fine-tune Query
Hi, I have a query as mentioned below: SELECT BillCurrencyID,DistD,Amount,PartnerFlag1 FROM Factsales_tab_Dtls (NOLOCK) WHERE (sales_year =2007 OR sales_year=2008) AND Country ='US' 1) Table Factsales_tab_Dtls is having more than 5.5 million of records. 2) It is having 32 columns. 3)There's nonclusetered index on columns sales_year & country 4)The query takes longer time for execution Please help me fine-tune the query
View Replies !
|
TRACKER
