SQLTeam.com | Weblogs | Forums

Newbie - table speed

sql2012
#1

Hi All - first post...

Background - we are a charity organising 1200 events a year - for the past 20 years, and our Access front end holds much of the data for all these events linked to an SQL server

Last year we moved our SQL sever from the office into the cloud, and since then the performance of my forms and queries have been rubbish! probably due to my poor programming and sql skills. But more on that another time - this question is more basic.

I have one table for instance, it has about 100 fields and 13000 records.

If I try to open that table in access - it literally take 4-5 minutes or more to show up.

I just get 'calculating' in the bottom left corner.

Would that be normal kind of speed for that amount of data? I have good broadband speed. The table has a Primary Key.

Any thoughts?

Many thanks

Ian
Director Arts Alive and Flicks in the Sticks.

0 Likes

#2

The answer will always be "It Depends" :wink:

Are all 100 columns huge (i.e. varchar(max))? Can you provide the table structure and some sample data? When you say open the table, are you performing a select * from the table and selecting all the rows? what happens if you select top (100) * from the table or Select top (100) Col1, Col2, etcc. from the table and limit the number of columns? It should not take 4-5 minutes, but i've seen some log tables developed by .Net developers that are all varchar(max) columns and is dog-slow. Sounds like an opportunity for a redesign

0 Likes

#3

Hi Mike

Thanks for your reply.

There are a huge range of columns, i'll list them below.

I am in Access and looking at the Linked Table.

EventID int Unchecked
venueID int Unchecked
promoterID int Unchecked
datefield datetime Checked
cluster varchar(50) Checked
filmCopyID int Checked
time varchar(50) Checked
Technician int Checked
AdultTP money Unchecked
FamilyTP money Unchecked
ChildTP money Unchecked
Other1TP money Unchecked
Other2TP money Unchecked
[Total Box Office] decimal(8, 2) Unchecked
[total box office net] decimal(8, 2) Unchecked
[Film Booked] bit Unchecked
filmbookingemailed bit Unchecked
filmbankemailconf bit Unchecked
[Filmbank Confirmation Rcvd] bit Unchecked
[film paid] bit Unchecked
[Inc Due] decimal(8, 2) Checked
[Inc Due less vat] decimal(8, 2) Checked
[Feedback back] bit Unchecked
[Tickets Sent] bit Unchecked
[Office notes] ntext Checked
[Adults Sold] int Unchecked
[Child Sold] int Unchecked
[Family SOld] int Unchecked
Comps int Unchecked
otherticket1 int Unchecked
otherticket2 int Unchecked
[total audience] int Unchecked
Capacity smallint Checked
a4publicity smallint Checked
[a5 publicity] smallint Checked
[film notes field] ntext Checked
[tech pay] numeric(18, 2) Checked
[tech paid] bit Unchecked
[last saved] datetime Checked
cancelled bit Unchecked
[poster sent date] datetime Checked
[Teccie Conf] bit Checked
carriagecostetc decimal(8, 2) Checked
[carlton confirmed] bit Checked
expectedfilmfee decimal(8, 2) Checked
[rights fee] decimal(8, 2) Checked
box varchar(50) Checked
COMMERCIALRTNDONE bit Checked
accountingcluster varchar(50) Checked
filmpaidRECONCILED bit Checked
promopaidRECONCILED bit Checked
timestamp timestamp Checked
feedbackemail bit Checked
dvdmovements int Checked
flicksfeedbackleft bit Checked
OverToQbooks bit Checked
FilmScore int Checked
PromoterComments ntext Checked
owndvd bit Checked
publiccomments text Checked
volhours numeric(18, 0) Checked
noofvols numeric(18, 0) Checked
aarating numeric(18, 0) Checked
rewarding numeric(18, 0) Checked
issues char(100) Checked
weather char(10) Checked
typeofscreening char(10) Checked
region char(10) Checked
InvoiceNo varchar(50) Checked
InvoiceDate datetime Checked
vatamount decimal(8, 2) Checked
totalvat decimal(8, 2) Checked
totalinv decimal(8, 2) Checked
posterlessvat decimal(8, 2) Checked
postervat decimal(8, 2) Checked
postertotal decimal(8, 2) Checked
testmoney smallmoney Checked
nonchargeable bit Checked
flicksfeedbackauthorised bit Checked
filmbankinv varchar(50) Checked
filmbankfilmin bit Checked
paid bit Checked
feedbackemailsent bit Checked
bookingfee decimal(8, 2) Checked
dvdmovecoments varchar(500) Checked
specialevents varchar(1000) Checked
didposters bit Checked
invoiceDetails ntext Checked
ukfcreported bit Checked
terrisnotes varchar(1000) Checked
videoID nvarchar(50) Checked
SellTicketsOnline bit Checked
WEBadult int Unchecked
WEBchild int Unchecked
WEBfamily int Checked
WEBadultSold int Checked
WEBchildSold int Checked
WEBfamilySOLD int Checked
LastScreeningInfo bit Unchecked
folio nvarchar(15) Checked
totinv numeric(8, 2) Checked
TeccieNotes varchar(1000) Checked
FlicksSoldOut bit Checked
0 Likes