Hello,
I have a unique situation that I only have limited control over. I was
hoping someone on the list could provide some assistance.
Here is my issue. A have a CF Site on a shared hosting account at
Crystaltech. The database is Access. This was fine for a certain
amount of time but now either there is too much activity on the shared
box or the query are returning too much data that the CFQUERY timeout is
becoming and issue.
The data in access is being supplied from the client via FTP and the CF
app accesses it. It is over written over night. I can change the way
the client gives me the data it is an automated process.
So I was thinking that I would mirror the exact tables in MS SQL then
once a night select all the content from Access and insert it into SQL.
Then have all my users access the SQL tables.
Sounds logical, the only issue is that I can get the all the records to
insert into the MSSQL database in a timely fasion. I keep getting the
CFQERY timeout.
Maybe its the way that I am doing it that is inefficient. I am using
both DSN like so,
<cfquery name="GetPayments" datasource="#request.AccessDsn#">
Select * from Client_Pmt
</cfquery>
<cfoutput query="GetPayments">
<cfquery datasource="#request.MSSQLDsn#"
name="qry_insertPayments">
insert into Client_Pmt
(Acct_id, PMT_Date,Pmt_Code,PMTAMT)
Values (#Getpayments.DEBT_ID#,
'#Getpayments.PMT_DATE#', '#Getpayments.PMT_CODE#', #Getpayments.PMTAMT#)
</cfquery>
</cfoutput>
Is there a better way to do this?
Thanks
Mike
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67
Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220500
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
