February 6, 2008 at 1:38 am
Dear Pals,
TIMEOUT EXPIRED WHILE TRYING TO EXECUTE A VIEW MANUALLY FROM SQL SERVER 7. PLEASE NOTE THAT IAM NOT USING ANY APPLICATION TO PERFORM THIS QUERY USING DSN. . THE MDF FILE SIZE IS AROUND 12 GB. WE CLEAR THE LOG FILE ON A DAILY BASIS AFTER TAKING A BACKUP SO THE LOG FILE SIZE IS IN MB'S ONLY. THE DISK SPACE AVAILABLE ON THE SERVER FOR DATA ALONE IS 120 GB & REMAINING SPACE IS ONLY 11.9 GB. AT THE END OF THE DAY WE CLEAR ALL THE LOG FILES & KEEP THE DISK SPACE FREE ALMOST 50 % OF THE TOTAL.
I WOULD REALLY APPRECIATE IF SOMEONE COULD THROW SOME LIGHT ON THIS. THANKS IN ADVANCE
REGARDS,
RAJESH
February 6, 2008 at 2:30 am
Please don't post in caps. It's the online equilalent of shouting, and it's kinda hard to read.
Could you please post the definition of the view, the schema and indexes of the tabls used by the view, and an approximate row count?
Thanks
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2008 at 3:55 am
Dear Gail,
First of all am sorry for my typing etiquittes. Since I was working on RPG programming at that time i left the caps lock on. Anyhow thanks for the response. Please find the view details.
select count(*) as ct, substring(tsdt, 5, 2) as mnth,
tscc as cntry from ict where (tpst <> 'c') and (tsdt between '20070101' and '20071231') group by substring(tsdt, 5, 2), tscc
There are approximately 1.8 million records in the table. We are not using indices on the table. The schema is given below. Sorry once again for my typing etiquittes.:hehe:
Regards,
Rajesh
CREATE TABLE [dbo].[ICT] (
[tICN] [decimal](9, 0) NOT NULL ,
[tSDT] [char] (8) NOT NULL ,
[tSTM] [char] (8) NOT NULL ,
[tUI1] [char] (15) NOT NULL ,
[tUI2] [char] (15) NOT NULL ,
[tUI3] [char] (15) NOT NULL ,
[tSCC] [decimal](3, 0) NOT NULL ,
[tSAG] [decimal](12, 0) NOT NULL ,
[tSCR] [char] (3) NOT NULL ,
[tSRT] [decimal](18, 9) NOT NULL ,
[tICR] [decimal](18, 9) NOT NULL ,
[tTVD] [char] (8) NOT NULL ,
[tSMD] [char] (6) NOT NULL ,
[tBAK] [char] (40) NOT NULL ,
[tCQN] [char] (20) NOT NULL ,
[tCDT] [char] (8) NOT NULL ,
[tSNM] [char] (35) NOT NULL ,
[tSD1] [char] (35) NOT NULL ,
[tSD2] [char] (35) NOT NULL ,
[tSD3] [char] (35) NOT NULL ,
[tSTL] [char] (25) NOT NULL ,
[tMNO] [char] (20) NOT NULL ,
[tBC1] [char] (5) NOT NULL ,
[tTYP] [char] (1) NOT NULL ,
[tRAG] [decimal](12, 0) NOT NULL ,
[tRCC] [decimal](3, 0) NOT NULL ,
[tRNM] [char] (35) NOT NULL ,
[tRD1] [char] (35) NOT NULL ,
[tRD2] [char] (35) NOT NULL ,
[tRD3] [char] (35) NOT NULL ,
[tRTL] [char] (25) NOT NULL ,
[tRCR] [char] (3) NOT NULL ,
[tRRT] [decimal](18, 9) NOT NULL ,
[tAMT] [decimal](14, 3) NOT NULL ,
[tCRG] [decimal](10, 3) NOT NULL ,
[tTAM] [decimal](14, 3) NOT NULL ,
[tRAM] [decimal](14, 3) NOT NULL ,
[tQUS] [char] (50) NOT NULL ,
[tANS] [char] (50) NOT NULL ,
[tMS1] [char] (50) NOT NULL ,
[tMS2] [char] (50) NOT NULL ,
[tINF] [char] (1) NOT NULL ,
[tIDN] [char] (30) NOT NULL ,
[tRFR] [char] (25) NOT NULL ,
[tRU1] [char] (15) NOT NULL ,
[tRU2] [char] (15) NOT NULL ,
[tRU3] [char] (15) NOT NULL ,
[tPMD] [char] (6) NOT NULL ,
[tAMC] [decimal](14, 3) NOT NULL ,
[tAMQ] [decimal](14, 3) NOT NULL ,
[tBNK] [char] (35) NOT NULL ,
[tCHD] [char] (8) NOT NULL ,
[tCHN] [char] (20) NOT NULL ,
[tPAG] [decimal](12, 0) NOT NULL ,
[tRDT] [char] (8) NOT NULL ,
[tRTM] [char] (8) NOT NULL ,
[tPST] [char] (1) NOT NULL ,
[tMR1] [char] (50) NOT NULL ,
[tMR2] [char] (50) NOT NULL ,
[tBNM] [char] (35) NOT NULL ,
[tBAC] [char] (20) NOT NULL ,
[tBA1] [char] (35) NOT NULL ,
[tBA2] [char] (35) NOT NULL ,
[tLRG] [decimal](7, 2) NOT NULL ,
[tLCD] [char] (3) NOT NULL ,
[tSTT] [char] (1) NOT NULL ,
[tSST] [char] (1) NOT NULL ,
[tRST] [char] (1) NOT NULL ,
[tSNO] [char] (8) NOT NULL ,
[tSND] [char] (8) NOT NULL ,
[tRNO] [char] (8) NOT NULL ,
[tRCD] [char] (8) NOT NULL ,
[tVNO] [char] (8) NOT NULL ,
[tVDT] [char] (8) NOT NULL ,
[tRVO] [char] (8) NOT NULL ,
[tRRD] [char] (8) NOT NULL ,
[tLGN] [char] (10) NOT NULL ,
[tLUD] [char] (8) NOT NULL ,
[tSTS] [char] (1) NOT NULL ,
[tBM1] [char] (50) NOT NULL ,
[tBM2] [char] (50) NOT NULL ,
[tTA1] [decimal](14, 3) NOT NULL ,
[tTA2] [decimal](14, 3) NOT NULL ,
[tFL1] [char] (1) NOT NULL ,
[tFL2] [char] (1) NOT NULL ,
[tVN1] [char] (6) NOT NULL ,
[tVN2] [char] (6) NOT NULL ,
[tVN3] [char] (6) NOT NULL ,
[tVN4] [char] (6) NOT NULL ,
[tVN5] [char] (6) NOT NULL ,
[tVN6] [char] (6) NOT NULL ,
[tBCD] [char] (4) NOT NULL ,
[tGCD] [char] (6) NOT NULL ,
[tICD] [char] (8) NOT NULL
) ON [PRIMARY]
February 6, 2008 at 4:17 am
No indexes and 2 million rows. Ouch. Does the table have a primary key?
I can suggest an index that is almost guaranteed to make this view faster. However, it's not likely to help other queries. If there's no pk, you may have duplicate data, etc, etc. That's a larger issue that may need some careful consideration on your side.
Is that table a copy of a mainframe table or similar?
To make this view faster, add an index on the following columns.
tsdt, tpst, tscc
tsdt first, because the between is most likely to reduce the number of rows for consideration the fastest.
tpst next for that inequality.
tscc to make the query covering.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 6, 2008 at 4:19 am
You could use an index on tsdt if the range is selective enough
Why are there so many char() fields? Varchar is usually smaller
February 6, 2008 at 4:23 am
Is there a reason you can not create an index even if only for the life of the query? A clustered index on your date field and non-clustered on the other field(s) in the where clause should help - no?
Toni
February 6, 2008 at 5:00 am
thx for the instant response gail. Yeah u rite! the table is being imported from DB2/AS400. There are reasons that we could not create the index since we are dropping the entire table & recreating the table everyday through a DTS from DB2 to SQL as that was our requirement. Moreover when i tried creating the said indices the SQL server is not reponding. This issue is chewing my brains out. :crazy: Please throw some light buddy...
Regards,
Rajesh
February 6, 2008 at 5:07 am
A small nonclustered index (like what I suggested) shouldn't take too long. A clustered index will take a while. Depends on the hardware and the memory available how long
If you want to implement a primary key (and you should consider it), see if you can revise your import so it either truncates the table and reloads or, preferably, just adds new records from DB2.
What I've seen suggested before - before you load data, drop all the nonclustered indexes, but leave the clustered index. Then, after the load recreate the nonclustered indexes.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
February 7, 2008 at 1:50 am
hi gail,
thx for the patience u hv showed, detailed diagnosis & the appropriate solution. I have done the same thng in SQL 2005 & it works fine now. But still I hv some constraints in SQL 7 bcoz of which I wasn't successful. anyhow ur scholarly advice hv opened new gates for newer learnigs to me. By the way I am giving my MCTS exams on SQL 2005 soon (70-431). Cud u guide me hw the simulation part should be handled? I wud really appreciate if u cud gimme some lab excercises for the same.
I also sincerly thank Jo & Toni for lending their helping hands. Hope to share the knowledge with u all in future too. Thank u all once again.
Regards,
Rajesh
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply