Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Optimising SQL to execute faster

Author  Topic 

johncogan
Starting Member

20 Posts
Posted - 2003-11-17 : 08:37:03
Hi

We are running into problems with our database. It has 51500 records in two tables (holding different but related data) and we are using ASP to execute SQL on the tables to produce reports which are simply COUNT with various WHERE statements.

The scripts and SQL has been running fine for over a year now, but since the database has exploded in size it is taking so long to execute these SQL statements that the ASP script is timeing out.

I have run the said statements through on SQL Query analyser and it takes over 5 minutes for each one. No change either when I create the statements within a stored procedure.

We can't delete any of the records (don't ask but its a client request)

One of the stements for example is:

SELECT COUNT(Applicants.ID) FROM Applicants INNER JOIN ApplicantData ON Applicants.ID = ApplicantData.ID WHERE Applicants.finished = 1 AND ApplicantData.Force1 = 'North Wales Police'

tried changing this to:

SELECT COUNT(ApplicantData.ID) FROM ApplicantData WHERE ApplicantData.force1 = 'North Wales Police' AND
ApplicantData.ID IN (
SELECT ID FROM Applicants WHERE finished = 1
)

but that made no difference.

Does anyone know how I can speed up the execution either through SQL code or some other method please?

Many Thanks
John

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2003-11-17 : 08:44:52
Do you have any indexes on Applicants and ApplicantData ?
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-11-17 : 08:50:44
Definitely use the first syntax, not the second. SQL probably optimizes them the same way, but I'd stick with the first.

I assume you have all the necessary indexes and foreign key relationships?

How many times are you executing these SELECT's to get the COUNT(*) of various things? Are you running 10 SELECT's to get 10 different counts from the same table? Do you know about the GROUP BY statement and how that works?

SELECT Applicantdata.Force1, COUNT(Applicants.ID) as Totals
FROM Applicants
INNER JOIN ApplicantData ON Applicants.ID = ApplicantData.ID
WHERE Applicationts.Finished = 1
GROUP BY ApplicantData.Force1

Or, if you want all your totals in 1 row, do you know about using a SUM(CASE ...) statement to do all your counts at once?

SELECT
SUM(CASE WHEN Force1 = 'North Wales Police' THEN 1 ELSE 0 END) as NWPolice,
SUM(CASE WHEN Force1 = 'South Wales Police' THEN 1 ELSE 0 END) as SWPolice,
..etc...
FROM Applicants
INNER JOIN ApplicantData ON Applicants.ID = ApplicantData.ID
WHERE Applicants.finished = 1

Finally, I saw a column called "Force1" ... you don't have multiple "Force" columns in that table, do you? Like Force1, Force2, Force3, .. etc... ? If so you need to read about database NORMALIZATION which says to break that out into a seperate table, which will make you database quicker and easier to maintain, and of course much faster to query.

I hope some of this helps.

- Jeff
Go to Top of Page

johncogan
Starting Member

20 Posts
Posted - 2003-11-17 : 09:36:01
Hi, cheers to both of you for your responses.

Ok will stick to the joins.

With regard to relationships and foreign keys etc, there is only a primary key on the ID column of each table. The tables aren't mine, they belong to a different developer but as far as I know there are no pyhsical constraints, relationships etc to the tables.

All the relationships that manage the data is donesuperficilly through the use of SQL statements with ASP.

As to indexes on the tables, nope non exist on these tables either as far as I know.

There are multiple columns for force1, force 2 and force3 but the statements for the reports are only interested in the force1 column, never the other two.

Cheers for your responses, going to have to try sort this all out, does make it easy either when the client owns the machine the SQL server in on :(
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2003-11-17 : 09:50:09
I would suggest adding a nonclustered index to ApplicantData.Force1.
Also it is always a good idea to use stored procedures.
Go to Top of Page

Wanderer
Master Smack Fu Yak Hacker

1168 Posts
Posted - 2003-11-17 : 09:53:44
No indexes ?

consider Index Finished,ID on the applicants table. (that depends on the cardinality of the finished data)
consider index Force1 on the ApplicantData table.

Check you estimated execution cost before indexes (Ctrl+L in QA), then add indexes, and review again. Look to ensure that your accesses are index seeks.

HTH

*#&#* *#&#* *#&#* *#&#*

Chaos, Disorder and Panic ... my work is done here!
Go to Top of Page

johncogan
Starting Member

20 Posts
Posted - 2003-11-17 : 10:24:54
Hi again, cheers for all your pointers.

I have added indexes on the suggested columns and checked the cost of the query before and after. Table scan cost dropped 3%.

What I am more worried about at this point is the fact that the indexes are going to be on two tables which experience a high amount of INSERT and UPDATE traffic on it each day.

Do indexes not slow this down considerably? Apart from the fact that they increases the database size by roughly 20%?

Is there any other way to do this, the prime requisites for any changes should be:

1) Increase in SQL execution time.
2) No (or negligible) increase in client waiting time (IE: User inputting data from web browser on a 10 page form)
3) No (or negligible) increse in database size.

Ta
John
Go to Top of Page

jsmith8858
Dr. Cross Join

7423 Posts
Posted - 2003-11-17 : 10:36:25
honestly, i would focus more on how you are querying these tables. Did you see my examples for different ways of selecting all the COUNT's you need all in 1 pass? Are you running lots of SELECT's one after another to get your various COUNT's currently?

1 SELECT will outperform 20 SELECTs, I can guarantee you that much.

- Jeff
Go to Top of Page

johncogan
Starting Member

20 Posts
Posted - 2003-11-17 : 10:52:40
Hi jsmith8858

Yep I know what you are saying but on the first page thats causing the problem there are only two SELECT statements. There is one page that writes a large report and does numerous SELECTs and will take your suggestion regarding the SUM and GROUP BY keywords into account when I get there.

The problem is that the database is so large it timesout even on simple statements on the introduction page, IE one or two SELECTs...

Even if I make a saving of 50% on the time taken to run a statement will still mean a 2 and 1/2 minute run for a query.

Something to me seems totally out of place or incorrect in this setup.

What I will do is time the query in QA with existing statements and compare to the time taken with the queries you suggested and see where that gets to.

Cheers.
John
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-11-17 : 11:04:58
First, it's Jeff

Second, how is 50,000 rows a lot? or 500,000

Just to do a SELECT COUNT()?

It should fly...

And where are you executing the SQL...dynamically?

And if you added an index and you're still scanning the table, then you added the wrong index...

Can you post the ddl (including the indexes) for the 2 tables?



Brett

8-)
Go to Top of Page

johncogan
Starting Member

20 Posts
Posted - 2003-11-17 : 11:46:39
Hi

Yep well this is exacxtly what I thought, 50000 rows in each table should not make much of a difference.

Some background on the SQL server. The server is a single CPU and serves a number of websites, although the one we use has the most traffic.

As to the SQL generation. Its been built on the fly and processed through an ASP recordset and connection object. Not the best way to go considering stored procedures are available. I did test the execution time of a SPROC on the system and it took the same amount of time as the existing method.

I think I am going to have to build a DTS package and schedule it to run once a day to create all my figures for the reports, this is beginning to seem like the most painless way to go.

Here are the tables as CREATE statements, no indexes on these and the COLLATE keywords need to be removed as QA adds these.

--------------------------------------------------------------------
CREATE TABLE [ApplicantData] (
[ID] [int] NOT NULL ,
[Address_1] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address_2] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address_County] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address_MonthofOccupancy] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address_Postcode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address_Town] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address_YearofOccupancy] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Age] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CountryOfBirth] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth_Day] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateOfBirth_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EMailAddress] [varchar] (150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Force1] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Force2] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Force3] [varchar] (29) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forename] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HighPotentialDevelopmentScheme] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[HomeTelephone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MobileTelephone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Nationality] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SpecialArrangementsAssessment] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[StayFreeOfRestrictions] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Surname] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SurnameAtBirth] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Title] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[WorkTelephone] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Court_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Court_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Court_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Court_4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Date_1] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Date_2] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Date_3] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Date_4] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Offence_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Offence_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Offence_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Offence_4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Result_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Result_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Result_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ConvictedOrCautioned_Result_4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InCriminalInvestigation] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InCriminalInvestigation_Detail] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tattoos] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Tattoos_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DaysOffWork] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OccasionsOffWork] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AgreementWithCourt] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AgreementWithCourt_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BeenBankrupt] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BeenBankrupt_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BeenBankrupt_Discharged] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BeenBankrupt_Discharged_Detail] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BusinessWithLicence] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BusinessWithLicence_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CourtActionForDebt] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CourtActionForDebt_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditCardWithdrawn] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CreditCardWithdrawn_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentBusinessInterest] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CurrentBusinessInterest_Detail] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InArrears] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InArrears_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoanTerminated] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LoanTerminated_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RepossessionProceedings] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RepossessionProceedings_Detail] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TribunalJudgement] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TribunalJudgement_Cleared] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TribunalJudgement_Cleared_More] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TribunalJudgement_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MorePreviousAddress] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress1_From_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress1_From_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress1_To_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress1_To_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress2_From_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress2_From_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress2_To_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress2_To_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress3_From_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress3_From_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress3_To_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddress3_To_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousAddresses] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Address_1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Address_2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Address_3] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Address_4] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Address_5] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Address_6] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Address_7] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Address_8] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_DatePlaceofBirth_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_DatePlaceofBirth_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_DatePlaceofBirth_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_DatePlaceofBirth_4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_DatePlaceofBirth_5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_DatePlaceofBirth_6] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_DatePlaceofBirth_7] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_DatePlaceofBirth_8] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_More] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Name_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Name_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Name_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Name_4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Name_5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Name_6] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Name_7] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Name_8] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Relationship_1] [varchar] (49) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Relationship_2] [varchar] (49) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Relationship_3] [varchar] (49) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Relationship_4] [varchar] (49) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Relationship_5] [varchar] (49) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Relationship_6] [varchar] (49) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Relationship_7] [varchar] (49) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Family_Relationship_8] [varchar] (49) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employed] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Present_Name] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Present_Notice] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Present_ReasonLeft] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Present_Title_Dates] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Name_1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Name_2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Name_3] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Name_4] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Name_5] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_ReasonLeft_1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_ReasonLeft_2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_ReasonLeft_3] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_ReasonLeft_4] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_ReasonLeft_5] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Title_Dates_1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Title_Dates_2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Title_Dates_3] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Title_Dates_4] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Employment_Prev_Title_Dates_5] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_CommandingOfficer] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_DischargeDate_Day] [varchar] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_DischargeDate_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_DischargeDate_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_Rank] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_ReasonForDischarge] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_Reservist] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_Reservist_Details] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_Served] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_ServedDates_FromMonth] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_ServedDates_FromYear] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_ServedDates_ToMonth] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_ServedDates_ToYear] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_ServedIn] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_ServedIn_Other] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_ServiceNumber] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_UnitAddress] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_UnitAddress_Postcode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Forces_UnitAddress_Telephone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee1_Address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee1_ContactNow] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee1_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee1_PositionHeld] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee1_Telephone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee2_Address] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee2_ContactNow] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee2_Name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee2_PositionHelp] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Referee2_Telephone] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_1_Force] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_1_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_1_Post] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_1_Result] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_1_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_2_Force] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_2_Month] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_2_Post] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_2_Result] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_2_Year] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousApplications_More] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousService] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousService_Force] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousService_MonthFinish] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousService_MonthStart] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousService_ReasonLeft] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousService_Role] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousService_YearFinish] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PreviousService_YearStart] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OtherSkills] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_1] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_2] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_3] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_4] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_5] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_6] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_7] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_8] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_9] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_10] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_11] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_12] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_13] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_14] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_15] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Grade_16] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_More] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_1] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_2] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_3] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_4] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_5] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_6] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_7] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_8] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_9] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_10] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_11] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_12] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_13] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_14] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_15] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_Subject_16] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_1] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_3] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_4] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_5] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_6] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_7] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_8] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_9] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_10] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_11] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_12] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_13] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_14] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_15] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Qualifications_YearTaken_16] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_1st_FullPartTime] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_1st_Name] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_1st_YearLeft] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_1st_YearStarted] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_FullPartTime_1] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_FullPartTime_2] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_FullPartTime_3] [varchar] (9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_Name_1] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_Name_2] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_Name_3] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_YearLeft_1] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_YearLeft_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_YearLeft_3] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_YearStarted_1] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_YearStarted_2] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SchoolCollege_YearStarted_3] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q1_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q1_2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q1_3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q1_4] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q2_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q2_2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q2_3] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q3_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q3_2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q4_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q4_2] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q5_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q6_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q7_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Competency_Q8_1] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Declaration] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EqualOpportunitiesAge] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EthnicOrigin] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[EthnicOrigin_Other] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Gender] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CareersOffice] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CouldYou_Online] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CouldYou_Online_Site] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CouldYou_Press] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CouldYou_Press_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CouldYou_Radio] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CouldYou_Radio_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CouldYou_TV] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_CouldYou_TV_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Internet] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Internet_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_JobCentre] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_JobCentre_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_MediaWhichPrompted] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Cinema] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Cinema_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Event] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Event_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_LocPress] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_LocPress_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_NatPress] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_NatPress_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Other] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Other_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Poster] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Poster_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Radio] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_Radio_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_TV] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_Other_TV_Which] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_PoliceOfficer] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Marketing_WordOfMouth] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
FOREIGN KEY
(
[ID]
) REFERENCES [Applicants] (
[ID]
)
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
--------------------------------------------------------------------
CREATE TABLE [Applicants] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[username] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[password] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[datestarted] [datetime] NULL ,
[datefinished] [datetime] NULL ,
[lastpage] [smallint] NULL ,
[finished] [bit] NOT NULL ,
CONSTRAINT [PK_Applicants] PRIMARY KEY NONCLUSTERED
(
[ID]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
Go to Top of Page

ehorn
Master Smack Fu Yak Hacker

1632 Posts
Posted - 2003-11-17 : 13:09:45
Now that explains it! You should seriously consider Normalizing the ApplicantData table.
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2003-11-17 : 13:34:07
Holy De-normalized table Batman!

You definatly need to Normalize that huge table. Almost anytime when you have fields with _1, _2, etc, you should probably consider making a table out of those. That way, instead of adding fields, you add new rows to that table.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

The Enigma
Posting Yak Master

179 Posts
Posted - 2003-11-17 : 15:28:50
quote:

Warning: The table 'ApplicantData' has been created but its maximum row size (15322) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.


That inspite of 44 text columns ...

:)

Attitude is everything

{The Enigma}
Go to Top of Page

MichaelP
Jedi Yak

2489 Posts
Posted - 2003-11-17 : 15:56:13
Woah, I didn't notice that part. Johncogan you REALLY need to rethink this table design. I think that you MUST normalize this table just to make your application work. Somthing tells me that once you normalize this thing, put the right indexes out there, this thing will be MUUUCH faster and easier to work with. Plus, it will WORK! Not trying to be harse here. Noone here is. We juwst want to let you know you've got some bigger problems that need to be dealt with. Those fixes should fix your performance issues as well. it's also going to allow you to have N number of those fields with numbers on the end.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 2003-11-17 : 16:00:35
I think I counted about a dozen tables in that one table....

Is this in production?



Brett

8-)
Go to Top of Page

The Enigma
Posting Yak Master

179 Posts
Posted - 2003-11-17 : 16:23:23
quote:

Transact-SQL
This forum is for Transact-SQL syntax. It will probably be mostly for developers but who knows what will show up in here. This is the place to discuss those nasty SELECT statements with 20 tables joined in.


lol ... this one entirely fits the description
quote:

We can't delete any of the records (don't ask but its a client request)

Brett... looks like its definitely in production

Attitude is everything

{The Enigma}
Go to Top of Page

Arnold Fribble
Yak-finder General

1961 Posts
Posted - 2003-11-17 : 17:18:48
"Tattoos_Details"
Is this normal?
Go to Top of Page

spireite
Starting Member

12 Posts
Posted - 2003-11-17 : 18:30:39
Bloody hell!! It makes my denormalised tables on a legacy system I inherited look normalised! This needs some work on it to say the least, but I suspect the impact of normalising it may result in quite a painful rewrite somewhere. However, if it was me I'd bite the bullet. What would be interesting would be the widest record you have...excluding the Text types.
Go to Top of Page

johncogan
Starting Member

20 Posts
Posted - 2003-11-18 : 04:25:03
Hehe Hi all

Cheers for all your responses.

I am been totally honest here. The applicationm wasn't designed by me, I was just asked to create a system to build reports on the data within the tables.

Yes I know what your saying in terms of the table been too large, waaaay too large but my hands are tied in this respect. For the following reason I cannot change the table.

a) Its in production and linked to an application that is constantly getting data. Therefore, no time to take it down and make the changes without opening a can of worms in terms of people missing applications.

Also, my hands are tied in terms of changes I can make to the database to make my reports work:

a) I cant make changes that will increase the size of the database as the clients server is already having problems with available disk capacity.
b) I cant make changes that will impact on the performance of the forms that gather the data (The application forms).
These two points above (I think) make it a no-no for indexes.

Something tells me because of the restrictions above that I have hit a dead end.

With regard to the "tattoo's", hehe nope this isn't an application for a job in a tattoo parlour. Its just that the people have to say whether they have tattoos visible on their arms or face etc...

Ah well, I really appreciate all the input all of you have given me, its not often you find a forum as helpful and friendly as this one.

Regards
John
Go to Top of Page

spireite
Starting Member

12 Posts
Posted - 2003-11-18 : 05:03:53
You may well find that this will come to a head. The reason I mentioned the cumulative rowsize in my previous response is because the system will fail with something along the lines of

Cannot create a row of size 8091 which is greater than the allowable maximum of 8060.

Since your maximum rowsize is 15322, but SQL wont allow more data per row than 8060, then when it happens you WILL have to change things.

If you can't change schema, I suggest the following.

1. Since the customer server is tight on diskspace, suggest they spend a few quid on extra drives. If this report/functionality is so important it can be justified.

2. Assuming 1. above, and your concerns of impact on performance with indexes, perhaps consider setting up a reporting database (on the extra disk), which you can index the hell out of, or even restructure.

3. Locate the DB developer

4. Shoot
Go to Top of Page
    Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site