Able to install Report Server component on non-domain web svr?

Hello all,
We are looking at installing Reporting Services at our company here. We wish
to install the Report Server component on an IIS 6 web server that is on our
DMZ network, and hence not in our domain (where the SQL Server resides that
we want to put the RS database.) While reading BOL's "Selecting Components
of Reporting Services to Install" chapter, it notes: "IMPORTANT: Report
server and the SQL Server instance hosting the report server database must
be in the same domain or trusted domain." In our case, they would not be. Is
this a deal-killer, or has someone successfully implemented this type of
architecture out there? Any comments would be greatly appreciated.
TIA,
Will DennisOne solution would be to put the Report Server almost anywhere on your
internal network, then access it via web services on your Internet-facing
server.
Look at the Report Server Control example, just modify the URL parameter.
Jeff
"Willard Dennis" <wdennis@.bayada.com> wrote in message
news:#CgzKzJWEHA.1380@.TK2MSFTNGP12.phx.gbl...
> Hello all,
> We are looking at installing Reporting Services at our company here. We
wish
> to install the Report Server component on an IIS 6 web server that is on
our
> DMZ network, and hence not in our domain (where the SQL Server resides
that
> we want to put the RS database.) While reading BOL's "Selecting Components
> of Reporting Services to Install" chapter, it notes: "IMPORTANT: Report
> server and the SQL Server instance hosting the report server database must
> be in the same domain or trusted domain." In our case, they would not be.
Is
> this a deal-killer, or has someone successfully implemented this type of
> architecture out there? Any comments would be greatly appreciated.
> TIA,
> Will Dennis
>

A weird problem of Reporting Service

My company is using SQL 2000, Reporting Service SP2, Server 2003, .NET framework 1.1.

We have an ASP.NET application, working properly on one of our test machine, but when we test on another test machine, it would have the following problem:

When we want to view the PDF or EXCEL report generated by Reporting Services, we get prompted to download an ASPX file. If we choose [Open], it will use Visual Studio.NET to open it. But if we choose [Save], and change the extenstion name, it's actually the correct report file.

That means, the report is generated correctly, but we can't open it.

We have added the Content-Disposition to indicate a correct extension name, but that machine still have this weird problem. Frustrately, it works perfectly on our another machine, even without Content-Disposition.

So, we are thinking probably it's related some file system security configuration. Could anyone give me a little hint about it?

Thank you very much!

Hi, we have the same problem. Did you get anywhere with this?|||are u still looking for answer of it?

that's because the server turns on the http compression, stupid IE doesn't understand it.

one thing to work it around is, turn off the compression for aspx, it might affect too much. If that's a concern, change your report generator file from ASPX to another extension, then that extension file won't have compression on. And you can enable asp.net to handle that new extension, which is only used to generate report.

this just works fine for us

A weird problem of Reporting Service

My company is using SQL 2000, Reporting Service SP2, Server 2003, .NET framework 1.1.

We have an ASP.NET application, working properly on one of our test machine, but when we test on another test machine, it would have the following problem:

When we want to view the PDF or EXCEL report generated by Reporting Services, we get prompted to download an ASPX file. If we choose [Open], it will use Visual Studio.NET to open it. But if we choose [Save], and change the extenstion name, it's actually the correct report file.

That means, the report is generated correctly, but we can't open it.

We have added the Content-Disposition to indicate a correct extension name, but that machine still have this weird problem. Frustrately, it works perfectly on our another machine, even without Content-Disposition.

So, we are thinking probably it's related some file system security configuration. Could anyone give me a little hint about it?

Thank you very much!

Hi, we have the same problem. Did you get anywhere with this?|||are u still looking for answer of it?

that's because the server turns on the http compression, stupid IE doesn't understand it.

one thing to work it around is, turn off the compression for aspx, it might affect too much. If that's a concern, change your report generator file from ASPX to another extension, then that extension file won't have compression on. And you can enable asp.net to handle that new extension, which is only used to generate report.

this just works fine for us

A weird problem of Reporting Service

My company is using SQL 2000, Reporting Service SP2, Server 2003, .NET framework 1.1.

We have an ASP.NET application, working properly on one of our test machine, but when we test on another test machine, it would have the following problem:

When we want to view the PDF or EXCEL report generated by Reporting Services, we get prompted to download an ASPX file. If we choose [Open], it will use Visual Studio.NET to open it. But if we choose [Save], and change the extenstion name, it's actually the correct report file.

That means, the report is generated correctly, but we can't open it.

We have added the Content-Disposition to indicate a correct extension name, but that machine still have this weird problem. Frustrately, it works perfectly on our another machine, even without Content-Disposition.

So, we are thinking probably it's related some file system security configuration. Could anyone give me a little hint about it?

Thank you very much!

Hi, we have the same problem. Did you get anywhere with this?|||are u still looking for answer of it?

that's because the server turns on the http compression, stupid IE doesn't understand it.

one thing to work it around is, turn off the compression for aspx, it might affect too much. If that's a concern, change your report generator file from ASPX to another extension, then that extension file won't have compression on. And you can enable asp.net to handle that new extension, which is only used to generate report.

this just works fine for us
sql

a visual basic script to DTS

Hello I am trying to convert a VB script someone wrote for my company into a DTS package that will fire automatically whenever the file is updated on the server. I am using vb.net for the firing of the DTS package, but I am having trouble writting the DTS package itself. This is what the code looks like now

If fso.FileExists(cSource) Then
With cne
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=" & cSource & ";" _
& "Extended Properties=Excel 8.0"
.Open
cSQL = "SELECT * FROM " & sSheet
Set rse = .Execute(cSQL)
End With

While Not rse.EOF
i = i + 1
Me.Label1.Caption = "Updating Sold: " & i
Me.Refresh
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
cn.Execute (cSQL)
End If
rse.MoveNext
Wend
rse.Close
cne.Close
End If


So basically I have an excel file that is downloaded once a week and then I run this program, well I am having some toubles with making it a DTS

I need to convert this into an active X transfermation code I think
If Not IsNull(rse("VEHICLE-STOCK-NO###")) Then
sSearch = "'" & rse("VEHICLE-STOCK-NO###") & "'"
cSQL = "UPDATE Inventory SET SoldDate = '" & rse("FNLZ-DT") & "', "
cSQL = cSQL & "LastUpdate = '" & Now & "' WHERE StockNo = " & sSearch
cSQL = cSQL & " AND SoldDate IS NULL"
end if

Thanks a lot

DamianSearch SQLDTS (http://www.sqldts.com) website for code examples and more information.

HTHsql

A query that is over my head...

Question:
How do I return a list of items that matches one or more criteria that I pass in?

Background:
A user enters a sales lead (a company is looking for a place to have their event). That lead has a number of
criteria elements (start date, end date, city, region, maximum room rate, one or more amenities, etc. - more
details below) that should be used when trying to find Hotels that match that criteria. Obviously, some
criteria is more important than others (city, start and end date are more important than the maximum room
rate) - and it's unlikely that many (if any) of the Hotels will match *all* of the criteria entered by the
user. So, I'm looking to return a list of Hotels that match at least one of the criteria - if possible,
ordered by how many criteria elements match.

What makes this query particularly difficult, is that some of the criteria to match are stored in multiple
tables. For example, each Hotel has "Amenities" (Golf, Spa, etc.) - that are stored in a seperate table.
When a user enters a lead, they select which amenities they want to match. Also, a lead specifies a number
of rooms to block for each day between the Arrival and Departure date - these numbers can change from day
to day - but for this query - I think it's acceptible to get the largest number of rooms needed from any of
the days and compare that one number against the "MaxDailyRoomBlock" field of a Hotel (represented by the
"Property" table). Also, since a Hotel has different rates defined for each season, the query will have to
match the "MaxRate" against the rate of the correct season based on the Arrival and Departure dates. Also,
the rate can be within 20% of the stated "MaxRate".

Here are the following variables that will be passed into the query as criteria items:
RequestCity, RegionINDEID, ArrivalDate, DepartureDate, MaxRate, MaxTheaterSeating, MaxBanquetSeating,
MaxSchoolSeating, MaxBreakoutRooms, MaxRoomBlock

I know this is a huge post - and I sincerly appreciate any help you can provide.

DDL for Tables:
*In the DDL.txt attachment

Sample Data:
* In the data.txt attachment

Previous Attempts:
Unfortunately, I don't even know where to begin, so I haven't tried anything yet.

Expected Results:

PropertyID Name NumOfMatches
------ ------ ----
1 Marriot San Diego 5
2 Hilton San Diego 3
3 Hilton San Diego Downtown 2

Thanks in advance, again...Sounds like you need weighted values, not just a count of how many matches.
You are going to need to do this in a stored procedure, and it will likely require serveral steps depending upon the complexity of the schema and business requirements.
I strongly suggest you find a DBA proficient in SQL programming to help you with this, as it could end up being a big job for you.|||I can give some enlightment to ur problem.place the mandatory certeria in ur where clause,(eg:regionID,startdate,enddate) and put the other certeria in ur case statement as follows.
I have given weightage 1 for every certeria,u can change as per ur requirement(u can see at 'case statment')

NB:I didnt understand ur 20% of Maxrate.give me a example


-- details--
select l.LeadID,p.PropertyID,p.Name,
case when l.MaxDailyRoomBlock<=p.MaxDailyRoomBlock then 1
else 0 end as block,
case when l.MaxBreakoutRooms<=p.MaxBreakoutRooms then 1
else 0 end as breaks,
case when l.MaxTheaterSeating<=p.MaxTheaterStyleSeats then 1
else 0 end as Theater,
case when l.MaxBanquetSeating<=p.MaxBanquetStyleSeats then 1
else 0 end as Banquet,
case when l.MaxSchoolSeating<=p.MaxSchoolStyleSeats then 1
else 0 end as School,

(select count(*) from LeadAmenity la,
PropertyAmenity pa where l.LeadID=la.LeadID
and la.AmenityID=pa.AmenityID and p.PropertyID=pa.PropertyID) as AmenityCount
from
Property p,
Lead l





-- summary---
select LeadID,PropertyID,Name,(block+breaks+Theater+Banqu et+School+AmenityCount) as NumOfMatches
from
(
select l.LeadID,p.PropertyID,p.Name,
case when l.MaxDailyRoomBlock<=p.MaxDailyRoomBlock then 1
else 0 end as block,
case when l.MaxBreakoutRooms<=p.MaxBreakoutRooms then 1
else 0 end as breaks,
case when l.MaxTheaterSeating<=p.MaxTheaterStyleSeats then 1
else 0 end as Theater,
case when l.MaxBanquetSeating<=p.MaxBanquetStyleSeats then 1
else 0 end as Banquet,
case when l.MaxSchoolSeating<=p.MaxSchoolStyleSeats then 1
else 0 end as School,

(select count(*) from LeadAmenity la,
PropertyAmenity pa where l.LeadID=la.LeadID
and la.AmenityID=pa.AmenityID and p.PropertyID=pa.PropertyID) as AmenityCount
from
Property p,
Lead l
) as tm order by LeadID,PropertyID


come back if u have any doubts|||Thanks a ton for your reply, and sorry for being so slow to say so - I've been overwhelmed by the same project that prompted this question. It's helped me out a lot... thanks!

Chad

A problem when opening PDF and EXCEL report

Hi, All
This is really weird. Here is the full story:
My company has a test site to test the application we are developing,
and the Reporting Server is on the same machine. Each developer has the
full application on their own machine so that they can test other parts
individually and check in to the test site later. But we are all using
the same webservice server (that test site machine) and the same report
database.
Now, when I run the report part locally, by locally, I mean I am
running the application in my own machine, but that application is
still loading the remote webservice to generate the report from the
remote database. The report generation is working correctly, I can open
the report and view.
But, if I run that part in the test site, it has a problem. It still
generates a report, but it will prompt a download window, and the
report extension name is always ASPX, (for pdf and excel file). If I
click Open, it will use Visual Studio to open it. If I click Save, and
change the extension name, it's a correct report.
More interesting, for PDF report, if the client machine has Adobe
Reader 7.0, it won't even prompt, instead, it would just show an empty
window, you know those empty windows, you can see the background
through that window, and if you move the window, the background will
follow. Yup, a kind of normal window. It only has that prompt window if
the client has Adobe Reader 6.0
Anyone has a clue how to solve this? Any help is appreciated, thank you
very much!
--
Best Regards!
SowenI saw this during the beta for 2005, but it cleared up once I went with the
released version of 2005. Has this machine ever had the beta on it?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139930602.042085.322510@.f14g2000cwb.googlegroups.com...
> Hi, All
> This is really weird. Here is the full story:
> My company has a test site to test the application we are developing,
> and the Reporting Server is on the same machine. Each developer has the
> full application on their own machine so that they can test other parts
> individually and check in to the test site later. But we are all using
> the same webservice server (that test site machine) and the same report
> database.
> Now, when I run the report part locally, by locally, I mean I am
> running the application in my own machine, but that application is
> still loading the remote webservice to generate the report from the
> remote database. The report generation is working correctly, I can open
> the report and view.
> But, if I run that part in the test site, it has a problem. It still
> generates a report, but it will prompt a download window, and the
> report extension name is always ASPX, (for pdf and excel file). If I
> click Open, it will use Visual Studio to open it. If I click Save, and
> change the extension name, it's a correct report.
> More interesting, for PDF report, if the client machine has Adobe
> Reader 7.0, it won't even prompt, instead, it would just show an empty
> window, you know those empty windows, you can see the background
> through that window, and if you move the window, the background will
> follow. Yup, a kind of normal window. It only has that prompt window if
> the client has Adobe Reader 6.0
> Anyone has a clue how to solve this? Any help is appreciated, thank you
> very much!
> --
> Best Regards!
> Sowen
>|||hi, Bruce
thanks for your reply.
But we are still using VS 2003, SQL 2000 and .NET framework 1.1|||Ahh, OK. What Service Pack are you on?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139933336.752696.258260@.g44g2000cwa.googlegroups.com...
> hi, Bruce
> thanks for your reply.
> But we are still using VS 2003, SQL 2000 and .NET framework 1.1
>|||for reporting service, SP2
for 2003 server, SP1,|||How are your viewing the report? In your own app? Report Manager? If your
own app are you using URL integration or web services? If in your own app,
does it work properly when you use Report Manager?
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139934523.097245.13230@.g44g2000cwa.googlegroups.com...
> for reporting service, SP2
> for 2003 server, SP1,
>|||via URL. sth like
http://localhost/mmis/ReportViewer/tabid/140/desktopdefault/+/Default.aspx,
(we are using DNN too.)
the code generating the report is correct.
It works properly if I run all application on my own machine's IIS.
It only has those problems if I run it on our test site (remotely)|||OK, I think what is happening is the Server 2003 internet security. Try
putting in the name of the machine instead of localhost and add the local
machine to the safezone.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139936364.847940.143500@.g44g2000cwa.googlegroups.com...
> via URL. sth like
> http://localhost/mmis/ReportViewer/tabid/140/desktopdefault/+/Default.aspx,
> (we are using DNN too.)
> the code generating the report is correct.
> It works properly if I run all application on my own machine's IIS.
> It only has those problems if I run it on our test site (remotely)
>|||do you mean adding our test site url in IE security trusted site?|||Here is what I suggest you do. Go to the test server. Open up IE and go to
report manager and make sure your reports and exports etc work as you they
should. Until you know it all works from report manager I wouldn't waste
much time wondering why it isn't working from your app. My guess is that you
will see some of the same issues there. Windows 2003 SP1 really protects
what web sites you can go to. You most likely need to add to trusted sites
in IE on the server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"sowen" <sowencheung@.gmail.com> wrote in message
news:1139937391.453464.226010@.z14g2000cwz.googlegroups.com...
> do you mean adding our test site url in IE security trusted site?
>|||it works on our test site.
It is frustrated that the same code actually works on our another
remote test site, I just found out.
Is there any security configuration issue I should pay attention to?
It's strange that the same thing working on one machine but not another
machine.|||I mean it works on the test site report manager. I can see everything
running properly. But I have the same problem if I run the ASP.NET
program to create the PDF or EXCEL report, the prompt window asks me to
download a ASPX file.
But if I run the same code on another test machine, there is no such a
problem.
strange...

a million dollar question.

As I posted before. The company I'm working for is planning to develop a new
system for our POS around the country. We want to find the best replication
model between SQL Servers.
The situation turns difficult for us since we won't have static IP's on each
POS but dynamically IP's assigned by Cable, DSL or any other ISP available.
So we may not be able to establish VPN's.
I know most of you may have plenty experience on this subject.
Actually we're doing this with Web Services and data encryption but we want
to take the best alternative.
With SQL Server CE this is easier because of replication through IIS. Is
there a similar way between SQL Servers so I can protect information with a
Certificate or something?
As you can see, I'm a newbie in this but I want to learn about it. If there's
a good course/seminar you can recommend me to take, I would really appreciate
it. If the best way to learn is trying and a good book (Paul recommended me
Hillary's one), I can take that too.
Thank you very much.
Omar Rojas.
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...ation/200510/1
Having dynamic ip address doesn't prevent you to use VPN. You can have the
POS computer VPN to your network. If you set the VPN to register in the DNS
you can get to the POS my computer name.
Also, you could (and I don't think is a good idea) hardcode the IP Address
on the user (so each time they connect in the VPN server they get the same
address).
Jos.
"Omar via droptable.com" <u14595@.uwe> wrote in message
news:557d1862fce85@.uwe...
> As I posted before. The company I'm working for is planning to develop a
> new
> system for our POS around the country. We want to find the best
> replication
> model between SQL Servers.
> The situation turns difficult for us since we won't have static IP's on
> each
> POS but dynamically IP's assigned by Cable, DSL or any other ISP
> available.
> So we may not be able to establish VPN's.
> I know most of you may have plenty experience on this subject.
> Actually we're doing this with Web Services and data encryption but we
> want
> to take the best alternative.
> With SQL Server CE this is easier because of replication through IIS. Is
> there a similar way between SQL Servers so I can protect information with
> a
> Certificate or something?
> As you can see, I'm a newbie in this but I want to learn about it. If
> there's
> a good course/seminar you can recommend me to take, I would really
> appreciate
> it. If the best way to learn is trying and a good book (Paul recommended
> me
> Hillary's one), I can take that too.
> Thank you very much.
> Omar Rojas.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200510/1
|||You can use web replication with SQL Server in much the same
way that you have used it with SQL CE. The differences are:
1. you must use SSL for SQL Server web replication (with
SQL CE you have the option not to)
2. the code you need to write if you are thinking of using
SQL Express as the subscribers is difficult and not well
documented. I'm referring here to implementing replication
using RMO programming. I've just been through this on a
large project and it was not fun.
Other than that, it's the same concept - an ISAPI DLL sits in
a virtual directory on IIS and brokers the replication to web-
connected subscribers.
Darren Shaffer
..NET Compact Framework MVP
Principal Architect
Connected Innovation
www.connectedinnovation.com
"Omar via droptable.com" <u14595@.uwe> wrote in message
news:557d1862fce85@.uwe...
> As I posted before. The company I'm working for is planning to develop a
> new
> system for our POS around the country. We want to find the best
> replication
> model between SQL Servers.
> The situation turns difficult for us since we won't have static IP's on
> each
> POS but dynamically IP's assigned by Cable, DSL or any other ISP
> available.
> So we may not be able to establish VPN's.
> I know most of you may have plenty experience on this subject.
> Actually we're doing this with Web Services and data encryption but we
> want
> to take the best alternative.
> With SQL Server CE this is easier because of replication through IIS. Is
> there a similar way between SQL Servers so I can protect information with
> a
> Certificate or something?
> As you can see, I'm a newbie in this but I want to learn about it. If
> there's
> a good course/seminar you can recommend me to take, I would really
> appreciate
> it. If the best way to learn is trying and a good book (Paul recommended
> me
> Hillary's one), I can take that too.
> Thank you very much.
> Omar Rojas.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200510/1
|||replication over the web is relatively easy but somewhat involved.
Points to keep in mind.
1) your path to the snapshot subdirectory should be /ftp or /FTPVD/ftp,
where FTPVD is the name of your FTP Virtual Directory
2) your ftp server should be entered as a fully qualified domain name. This
is the option in the Snapshot location tab called FTP Server Name\
3) use anonymous authentication. If you are using not using anonymous
authentication make sure you created a local machine account with the same
name and this account has list and read access to the files and folders
underlying the ftp virtual directory.
4) Your subscribers need to find a path to your publisher across the
internet. You should register your publisher using its netbios name what you
see by issuing a master.dbo.xp_cmdshell 'echo %computername%. Use this name
as your Server alias when configuring the publisher name in Client Network
Utility on your Subscribers. Make sure the Server alias is the Server name
(i.e. Publisher), the ServerName is the Fully Qualified Domain Name to your
Publisher, i.e. Publisher.MSDN.Microsoft.com. Make sure you use the tcp/ip
network library.
Finally you will find it easy to use the replication activeX controls to
deploy subscribers, use PublisherAddress and DistributorAddress for the FQDN
for your Publisher/Distributor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Omar via droptable.com" <u14595@.uwe> wrote in message
news:557d1862fce85@.uwe...
> As I posted before. The company I'm working for is planning to develop a
> new
> system for our POS around the country. We want to find the best
> replication
> model between SQL Servers.
> The situation turns difficult for us since we won't have static IP's on
> each
> POS but dynamically IP's assigned by Cable, DSL or any other ISP
> available.
> So we may not be able to establish VPN's.
> I know most of you may have plenty experience on this subject.
> Actually we're doing this with Web Services and data encryption but we
> want
> to take the best alternative.
> With SQL Server CE this is easier because of replication through IIS. Is
> there a similar way between SQL Servers so I can protect information with
> a
> Certificate or something?
> As you can see, I'm a newbie in this but I want to learn about it. If
> there's
> a good course/seminar you can recommend me to take, I would really
> appreciate
> it. If the best way to learn is trying and a good book (Paul recommended
> me
> Hillary's one), I can take that too.
> Thank you very much.
> Omar Rojas.
>
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...ation/200510/1

a good way to shrink log file when transactions running?

I am working in a large company where thousands new records will be added
into sql server 2000. Our proudcts are running for 24X7. Transactions could
be doing at anytime. So it is impossible to ask users to log off the system
when to shrink the log file.
Is there a good way to do a shrinking job no matter what transactions are
running?
Why are you shrinking it in the first place? If it grew that big before it
probably will again. See this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And no shrinking does not require you to log off the users.
Andrew J. Kelly SQL MVP
"bluesky" <bluesky@.discussions.microsoft.com> wrote in message
news:F4DFA538-19E6-4457-AE82-F4CA856C68B7@.microsoft.com...
>I am working in a large company where thousands new records will be added
> into sql server 2000. Our proudcts are running for 24X7. Transactions
> could
> be doing at anytime. So it is impossible to ask users to log off the
> system
> when to shrink the log file.
> Is there a good way to do a shrinking job no matter what transactions are
> running?
|||It is right that it does not require you to log off the users.

>From the BOL for DBCC SHRINKFILE (because you only want to shrink the
log file)
The database being shrunk does not have to be in single-user mode;
other users can be working in the database when the file is shrunk. You
do not have to run SQL Server in single-user mode to shrink the system
databases.
It will only shrink the file as much as it can (despite you may put a
target there) and do not touch those that are currently in use.
Mel

a good way to shrink log file when transactions running?

I am working in a large company where thousands new records will be added
into sql server 2000. Our proudcts are running for 24X7. Transactions could
be doing at anytime. So it is impossible to ask users to log off the system
when to shrink the log file.
Is there a good way to do a shrinking job no matter what transactions are
running?Why are you shrinking it in the first place? If it grew that big before it
probably will again. See this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And no shrinking does not require you to log off the users.
Andrew J. Kelly SQL MVP
"bluesky" <bluesky@.discussions.microsoft.com> wrote in message
news:F4DFA538-19E6-4457-AE82-F4CA856C68B7@.microsoft.com...
>I am working in a large company where thousands new records will be added
> into sql server 2000. Our proudcts are running for 24X7. Transactions
> could
> be doing at anytime. So it is impossible to ask users to log off the
> system
> when to shrink the log file.
> Is there a good way to do a shrinking job no matter what transactions are
> running?|||It is right that it does not require you to log off the users.

>From the BOL for DBCC SHRINKFILE (because you only want to shrink the
log file)
The database being shrunk does not have to be in single-user mode;
other users can be working in the database when the file is shrunk. You
do not have to run SQL Server in single-user mode to shrink the system
databases.
It will only shrink the file as much as it can (despite you may put a
target there) and do not touch those that are currently in use.
Mel

a good way to shrink log file when transactions running?

I am working in a large company where thousands new records will be added
into sql server 2000. Our proudcts are running for 24X7. Transactions could
be doing at anytime. So it is impossible to ask users to log off the system
when to shrink the log file.
Is there a good way to do a shrinking job no matter what transactions are
running?Why are you shrinking it in the first place? If it grew that big before it
probably will again. See this:
http://www.karaszi.com/SQLServer/info_dont_shrink.asp
And no shrinking does not require you to log off the users.
--
Andrew J. Kelly SQL MVP
"bluesky" <bluesky@.discussions.microsoft.com> wrote in message
news:F4DFA538-19E6-4457-AE82-F4CA856C68B7@.microsoft.com...
>I am working in a large company where thousands new records will be added
> into sql server 2000. Our proudcts are running for 24X7. Transactions
> could
> be doing at anytime. So it is impossible to ask users to log off the
> system
> when to shrink the log file.
> Is there a good way to do a shrinking job no matter what transactions are
> running?|||It is right that it does not require you to log off the users.
>From the BOL for DBCC SHRINKFILE (because you only want to shrink the
log file)
The database being shrunk does not have to be in single-user mode;
other users can be working in the database when the file is shrunk. You
do not have to run SQL Server in single-user mode to shrink the system
databases.
It will only shrink the file as much as it can (despite you may put a
target there) and do not touch those that are currently in use.
Mel

A Game Company in Trouble

My Name is Nick Soutter, I am the owner of a small game company, Aepox Games
(We're in the middle of a name change from "Lamar Games"),
www.lamargames.net.

Our first commercial game, Andromeda Online (www.andromedaonline.net) is
going into beta soon. It runs on an evaluation edition of SQL Server 2000
(our intention is, when it launches, we earn the money to buy a copy before
the evaluation expires).

We have been testing Andromeda Online, and found that saves to the database
take about 10 seconds (we were anticipating less than 1). We felt we need
somebody experienced in optimizing sql databases to help us optimize the
database, and get it running in the best method for our particular
application.

Our program accesses the database in Java, and people with understanding in
how to optimize java connections would be a tremendous help.

My company is small, and we honestly cant afford much. Everybody on this
project, from the sound guys to the graphic artist, has worked for 1/10 to
1/100 of the value of the job. We're simply a starting company looking for
dedicated people who are willing to work more for credit than money.

We can offer credit on our website
(http://www.andromedaonline.net/credits.html) to anybody who helps us, but
little more (maybe $100, but we're very over budget, and in desperate need
of help). Because of how we intend the game to run (with maybe 100-200
concurrent games running online), a 10 second save time is simply
unacceptable.

Anybody who would be willing to help us, please send a resume to
help@.andromedaonline.net. Experience would be nice, but not a requirement.
We're looking for someone who can talk with our programmer about the types
of calls made to our SQL database, and then can log into the DB and optimize
it to run as fast as possible considering our specific needs.

Thank you for your time.

Nick Soutter

Aepox (Lamar) GamesBlackHawke wrote:
> We have been testing Andromeda Online, and found that saves to the database
> take about 10 seconds (we were anticipating less than 1). We felt we need
> somebody experienced in optimizing sql databases to help us optimize the
> database, and get it running in the best method for our particular
> application.
>
> Our program accesses the database in Java, and people with understanding in
> how to optimize java connections would be a tremendous help.

Some simple advice, without knowing anything about your application.
It's highly unlikely that the problem is related to your use of Java-
based interfaces to the database. The problem is almost certainly with
the SQL that you are running, and is independent of the means of issuing
that SQL to the database itself. An exception applies, though, if
you're using some high-level abstraction like an OR mapper that
generates the SQL for you. Is this hand-written JDBC and SQL, or
something more complex?

> Anybody who would be willing to help us, please send a resume to
> help@.andromedaonline.net. Experience would be nice, but not a requirement.
> We're looking for someone who can talk with our programmer about the types
> of calls made to our SQL database, and then can log into the DB and optimize
> it to run as fast as possible considering our specific needs.

Given your financial constraints, it would perhaps be more prudent for
you to take advantage of the advice freely offered on this newsgroup.
You'd be expected to take part in finding the solution, by for example
putting together test cases and doing some troubleshooting, but if you
have a real question about a factual or difficult issue, someone will
almost certainly jump in to help.

A good place to start is to look over the statements that are issued in
a save. Many databases have a logging mode that can capture this info,
but if your database doesn't have such a mode or flag, you could do it
from the application or with a filter JDBC driver. So to start with the
easiest solution, which database are you using?

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation|||BlackHawke (blackhawke@.legacygames.net) writes:
> Our first commercial game, Andromeda Online (www.andromedaonline.net) is
> going into beta soon. It runs on an evaluation edition of SQL Server
> 2000 (our intention is, when it launches, we earn the money to buy a
> copy before the evaluation expires).

When did you download this evaluation edition? Beware that the original
evaluation edition is vulnerable for the Slammer worm. If you go to
http://www.microsoft.com/sql you can obtain a version of the Evaluation
Edition that is Slammer-safe.

> We have been testing Andromeda Online, and found that saves to the
> database take about 10 seconds (we were anticipating less than 1). We
> felt we need somebody experienced in optimizing sql databases to help us
> optimize the database, and get it running in the best method for our
> particular application.

What exactly are you saving? Does it take 10 seconds to save a single
row to the database? Or does it take 10 seconds to save a player's
entire game?

The latter could mean a whole lot calls to SQL Server to insert data.
If all operations are through INSERT statements sent from the Java
code, there is a whole lot to win by using stored procedures. For even
higher speed, you could construct an XML document, and then unpack that on
the SQL Server side with OPENXML(). You save a lot of network roundtrips
that way.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||To add to everybody else's statements.

Run Profiler against the DB and see exactly what is being sent. If you can
get statements out then you can look at the execution plans and perhaps that
will point out a reason for the preceived slowness.

--

---------
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org

"Erland Sommarskog" <sommar@.algonet.se> wrote in message
news:Xns93BC69EEEF67FYazorman@.127.0.0.1...
> BlackHawke (blackhawke@.legacygames.net) writes:
> > Our first commercial game, Andromeda Online (www.andromedaonline.net) is
> > going into beta soon. It runs on an evaluation edition of SQL Server
> > 2000 (our intention is, when it launches, we earn the money to buy a
> > copy before the evaluation expires).
> When did you download this evaluation edition? Beware that the original
> evaluation edition is vulnerable for the Slammer worm. If you go to
> http://www.microsoft.com/sql you can obtain a version of the Evaluation
> Edition that is Slammer-safe.
> > We have been testing Andromeda Online, and found that saves to the
> > database take about 10 seconds (we were anticipating less than 1). We
> > felt we need somebody experienced in optimizing sql databases to help us
> > optimize the database, and get it running in the best method for our
> > particular application.
> What exactly are you saving? Does it take 10 seconds to save a single
> row to the database? Or does it take 10 seconds to save a player's
> entire game?
> The latter could mean a whole lot calls to SQL Server to insert data.
> If all operations are through INSERT statements sent from the Java
> code, there is a whole lot to win by using stored procedures. For even
> higher speed, you could construct an XML document, and then unpack that on
> the SQL Server side with OPENXML(). You save a lot of network roundtrips
> that way.
>
> --
> Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||BlackHawke wrote:
> I suspect that most of the problem lies in how our database is configured.
> Knowing nothing about configuring these things, I set it up in the default
> settings. I've boosted the priority, and that helped, but I know nothing
> about enhancing the database response time.
> We are using MS SQL Server 2000... Is that what you meant by what database
> are we using? As far as how the JAVA code interacts with it, I'd have to ask
> the programmer.

Okay, please do ask the programmer to make an appearance here; there's
no reason to work through a level of indirection.

--
www.designacourse.com
The Easiest Way to Train Anyone... Anywhere.

Chris Smith - Lead Software Developer/Technical Trainer
MindIQ Corporation|||What database are you using Oracle/MSSQL/MySQL/Postgresql and the platform
NT/XP/Linux/BSD? Without knowing that there is no way to make any
recommendations regarding performance tunning.

"Christopher Browne" <cbbrowne@.acm.org> wrote in message
news:bf8u43$c4538$1@.ID-125932.news.uni-berlin.de...
> "BlackHawke" <blackhawke@.legacygames.net> writes:
> > My Name is Nick Soutter, I am the owner of a small game company, Aepox
Games
> > Our program accesses the database in Java, and people with understanding
in
> > how to optimize java connections would be a tremendous help.
> A few database performance principles are vital:
> 1. It's probably fruitful to find someone that knows something about
> SQL Server. There are likely some parameters that may be tuned that
> will improve performance a fair bit just by having someone turn a few
> "virtual knobs."
> 2. There is a pretty high startup cost when opening a connection or
> when first submitting a query.
> If the connection or prepared query can get reused over and over, that
> amortizes the startup cost.
> For instance, if they keep opening and closing the connection over and
> over again, that would be an excellent reason to expect performance to
> be terrible.
> When querying data, the usual rule is that while running one query to
> pull 100,000 rows of data is expensive, but not _too_ expensive; the
> flip case, of submitting 100,000 queries, each drawing 1 row, is a
> RIDICULOUS way of doing things, and will perform terribly. I'm not
> sure if Sybase designed "mass data load" facilities into their
> database (which later was bought by Microsoft), but it would be a
> little surprising if there wasn't.
> 3. Indexes can be really helpful.
> Certainly for improving query performance, but sometimes even on
> inserts, in terms of validating things.
> For instance, if the DB schema does integrity checks on foreign keys,
> you probably need to have indexes on those foreign tables in order for
> it not to behave like shaking up a bag of rocks.
> --
> wm(X,Y):-write(X),write('@.'),write(Y). wm('cbbrowne','ntlug.org').
> http://cbbrowne.com/info/lsf.html
> "How much more helpful could I be than to provide you with the
> appropriate e-mail address? I could engrave it on a clue-by-four and
> deliver it to you in Chicago, I suppose." -- Seen on Slashdot...|||If I failed to mention it earlier, SQL 2000, Evaluation Edition, on Windows
2003 Server, Eval Edition.

I'll ask the programmer to make an appearance ASAP.

Nick
"Ed Yu" <ekyu@.sc.rr.com> wrote in message
news:H1_Ra.247931$nr.9986970@.twister.southeast.rr. com...
> What database are you using Oracle/MSSQL/MySQL/Postgresql and the platform
> NT/XP/Linux/BSD? Without knowing that there is no way to make any
> recommendations regarding performance tunning.
> "Christopher Browne" <cbbrowne@.acm.org> wrote in message
> news:bf8u43$c4538$1@.ID-125932.news.uni-berlin.de...
> > "BlackHawke" <blackhawke@.legacygames.net> writes:
> > > My Name is Nick Soutter, I am the owner of a small game company, Aepox
> Games
> > > Our program accesses the database in Java, and people with
understanding
> in
> > > how to optimize java connections would be a tremendous help.
> > A few database performance principles are vital:
> > 1. It's probably fruitful to find someone that knows something about
> > SQL Server. There are likely some parameters that may be tuned that
> > will improve performance a fair bit just by having someone turn a few
> > "virtual knobs."
> > 2. There is a pretty high startup cost when opening a connection or
> > when first submitting a query.
> > If the connection or prepared query can get reused over and over, that
> > amortizes the startup cost.
> > For instance, if they keep opening and closing the connection over and
> > over again, that would be an excellent reason to expect performance to
> > be terrible.
> > When querying data, the usual rule is that while running one query to
> > pull 100,000 rows of data is expensive, but not _too_ expensive; the
> > flip case, of submitting 100,000 queries, each drawing 1 row, is a
> > RIDICULOUS way of doing things, and will perform terribly. I'm not
> > sure if Sybase designed "mass data load" facilities into their
> > database (which later was bought by Microsoft), but it would be a
> > little surprising if there wasn't.
> > 3. Indexes can be really helpful.
> > Certainly for improving query performance, but sometimes even on
> > inserts, in terms of validating things.
> > For instance, if the DB schema does integrity checks on foreign keys,
> > you probably need to have indexes on those foreign tables in order for
> > it not to behave like shaking up a bag of rocks.
> > --
> > wm(X,Y):-write(X),write('@.'),write(Y). wm('cbbrowne','ntlug.org').
> > http://cbbrowne.com/info/lsf.html
> > "How much more helpful could I be than to provide you with the
> > appropriate e-mail address? I could engrave it on a clue-by-four and
> > deliver it to you in Chicago, I suppose." -- Seen on Slashdot...|||While you may suggest the obvious such as indexing the proper columns, you
need to know much more about the system as a whole to even begin suggesting
scenerio that could be the bottleneck. In fact, even if you have follow all
the best RDBMS best practice (including tunning SQL by generating execution
plans), you may have chocked up your system if you are not careful about
creating large objects on the java end or misconfigurating the connection
pool. So, with that said, we really need to begin at point zero, which in
this case is:

1) Platform - Win2K
2) Database - MSSQL 2000 Eval
3) Java - ? a web application I assume, it is JSP? Servlet? Using any type
of framework Struts/WebWorks?
4) Web server - ? Apache/IIS/Tomcat?
5) Application Server - ? Tomcat/Weblogic/Websphere?
6) Physical system configuration - machine A (web server), machine B
(application server), machine C (database server)
7) Client - ? Http client/Applet/C/C++ clients?
8) Protocol - ? HTTP/HTTPS/Socket?

"Christopher Browne" <cbbrowne@.acm.org> wrote in message
news:bfa1fs$ci27r$1@.ID-125932.news.uni-berlin.de...
> "Ed Yu" <ekyu@.sc.rr.com> writes:
> > What database are you using Oracle/MSSQL/MySQL/Postgresql and the
platform
> > NT/XP/Linux/BSD? Without knowing that there is no way to make any
> > recommendations regarding performance tunning.
> You're very much missing the point.
> The three principles I presented are true regardless of platform and
> regardless of DBMS implementation. While different implementations
> may differ, there ARE some near universal principles.
> --
> (format nil "~S@.~S" "cbbrowne" "acm.org")
> http://www3.sympatico.ca/cbbrowne/rdbms.html
> "C++ is more of a rube-goldberg type thing full of high-voltages,
> large chain-driven gears, sharp edges, exploding widgets, and spots to
> get your fingers crushed. And because of it's complexity many (if not
> most) of it's users don't know how it works, and can't tell ahead of
> time what's going to cause them to loose an arm." -- Grant Edwards|||> 1) Platform - Win2K
> 2) Database - MSSQL 2000 Eval
> 3) Java - ? a web application I assume, it is JSP? Servlet? Using any type
> of framework Struts/WebWorks?
> 4) Web server - ? Apache/IIS/Tomcat?
> 5) Application Server - ? Tomcat/Weblogic/Websphere?
> 6) Physical system configuration - machine A (web server), machine B
> (application server), machine C (database server)
> 7) Client - ? Http client/Applet/C/C++ clients?
> 8) Protocol - ? HTTP/HTTPS/Socket?

Hello Everyone, I'm the lead programmer on this project. Thanks ahead
of time for any-and-all good advice coming from you fine folks :)
Well, this is a standard java application, no web-server involved. Im
using microsofts JDBC4 SQLServer driver for my connection to the
database, no ODBC (ugh!) involved. Also, Im using the apache commons
connection pool classes to handle all of the database connection
pooling as well as the player execution thread pooling.

I dont think we have any indexes setup on our tables just yet. Most of
the tables have the primary key set to an identity column I call
"IndexCol". The important tables have this ID column as well as a
"GameID", "PlayerID", "ShipID" fields to determin exactly which object
this row of data corresponds to. Do I want to setup the indexs on
these three columns?

The speed problems we see currently, arent on the SELECT end of the
spectrum, they are on the INSERT parts. I am using prepared statements
to "INSERT" the data row-by-row into the database. Im not using batch
mode at the moment. I've tried to make the tables as lean as possible,
factoring out columns into other tables where possible. The main ship
table is pretty large, maybe 100 columns of int/smallint/bit data, no
text. Right now, to update the game specifics, I delete all the data
in that table for the specific game, then I use the prepared
statements to re-insert the updated data. I dont want to use "UPDATE"
sql because some records aren't restored, thats why I like the
delete/insert combo.

If theres any other info I can send, please let me know.

Thanks, Greg.|||[Followups trimmed to comp.databases.ms-sqlserver, as this is the only
relevant group.]

DiscoStu (gregfrommer@.hotmail.com) writes:
> The speed problems we see currently, arent on the SELECT end of the
> spectrum, they are on the INSERT parts. I am using prepared statements
> to "INSERT" the data row-by-row into the database. Im not using batch
> mode at the moment. I've tried to make the tables as lean as possible,
> factoring out columns into other tables where possible. The main ship
> table is pretty large, maybe 100 columns of int/smallint/bit data, no
> text. Right now, to update the game specifics, I delete all the data
> in that table for the specific game, then I use the prepared
> statements to re-insert the updated data. I dont want to use "UPDATE"
> sql because some records aren't restored, thats why I like the
> delete/insert combo.

So, how many rows do insert when it takes 10 seconds to save a game?

The obvious improvement I see is to use stored procedures instead. If
that doesn't help, package data in an XML document and send this to
SQL Server, and then write a stored procedure that unpacks it with
OPENXML. Or use bulk-copy routines to load the data, but I have no
idea to do that from JDBC.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Without considering the physical server hardware and network configurations,
looks like you need a cluster index (not primary key) on the GAMEID column.
Since you do not have any index on this column, everytime someone starts the
same, you are doing a full table scan to locate and remove rows associated
with the GAMEID.

Looks like you need to read a little more about RDBMS application
programming and best pratices. BTW, I think for MSSQL, at least you need to
place the data files and log files on to seperate hardware (ie. disks
preferably on different controllers).

In addition, I am a little uneasy about using RDBMS for games since SQL
engines, IMHO, got too much overhead for a gaming application.

"DiscoStu" <gregfrommer@.hotmail.com> wrote in message
news:8457f9da.0307191352.2dbcbf2e@.posting.google.c om...
> > 1) Platform - Win2K
> > 2) Database - MSSQL 2000 Eval
> > 3) Java - ? a web application I assume, it is JSP? Servlet? Using any
type
> > of framework Struts/WebWorks?
> > 4) Web server - ? Apache/IIS/Tomcat?
> > 5) Application Server - ? Tomcat/Weblogic/Websphere?
> > 6) Physical system configuration - machine A (web server), machine B
> > (application server), machine C (database server)
> > 7) Client - ? Http client/Applet/C/C++ clients?
> > 8) Protocol - ? HTTP/HTTPS/Socket?
> Hello Everyone, I'm the lead programmer on this project. Thanks ahead
> of time for any-and-all good advice coming from you fine folks :)
> Well, this is a standard java application, no web-server involved. Im
> using microsofts JDBC4 SQLServer driver for my connection to the
> database, no ODBC (ugh!) involved. Also, Im using the apache commons
> connection pool classes to handle all of the database connection
> pooling as well as the player execution thread pooling.
>
> I dont think we have any indexes setup on our tables just yet. Most of
> the tables have the primary key set to an identity column I call
> "IndexCol". The important tables have this ID column as well as a
> "GameID", "PlayerID", "ShipID" fields to determin exactly which object
> this row of data corresponds to. Do I want to setup the indexs on
> these three columns?
> The speed problems we see currently, arent on the SELECT end of the
> spectrum, they are on the INSERT parts. I am using prepared statements
> to "INSERT" the data row-by-row into the database. Im not using batch
> mode at the moment. I've tried to make the tables as lean as possible,
> factoring out columns into other tables where possible. The main ship
> table is pretty large, maybe 100 columns of int/smallint/bit data, no
> text. Right now, to update the game specifics, I delete all the data
> in that table for the specific game, then I use the prepared
> statements to re-insert the updated data. I dont want to use "UPDATE"
> sql because some records aren't restored, thats why I like the
> delete/insert combo.
> If theres any other info I can send, please let me know.
> Thanks, Greg.|||Ed Yu (ekyu@.sc.rr.com) writes:
> Without considering the physical server hardware and network
> configurations, looks like you need a cluster index (not primary key) on
> the GAMEID column. Since you do not have any index on this column,
> everytime someone starts the same, you are doing a full table scan to
> locate and remove rows associated with the GAMEID.

Since the access problems were with saving, indexes are not so much of
an issue, although we don't know what the statements look like. INSERT
VALUES does not need any indexes, unless there is some trigger involved.
True, though, DiscoStu is deleting all data for a game first, and that
DELETE could use an index. I would suspect, however, that at this point
he don't have that much data in the database yet, so he has not yet
arrived at problems where indexes are the solution.

> Looks like you need to read a little more about RDBMS application
> programming and best pratices. BTW, I think for MSSQL, at least you need
> to place the data files and log files on to seperate hardware (ie. disks
> preferably on different controllers).

Undoubtedly this can be good for performance, but there are many systems
out there that don't have this configuration and still have good
performance. I don't think this is the right place to start.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Hal Berenson (haroldb@.truemountainconsulting.com) writes:
> Ok, let's start with something very basic. Are you wrapping the delete
> and inserts in a transaction, or is each statement a separate
> transaction? At a minimum all of the inserts should be inside a
> user-defined transaction.

Not talking of the fact that the DELETE where he sweeps the old data
should be part of that transaction too. Not so much for the speed of
the save, but for the integrity. If there is a crash before everything
is saved, the player should at least get his old save back. Not an
incomplete save from which he cannot continue, and neither nothing at
all.

> As for your general problem of lack of database expertise, I doubt that
> "credit" is going to get you all the help you need. You may have to look
> for a consultant who will trade time for a small amount of equity in your
> company.

Yes, the gaming company appears to be in dear need of venture capital
one way or another.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"BlackHawke" <blackhawke@.legacygames.net> writes:

> My Name is Nick Soutter, I am the owner of a small game company, Aepox Games
> (We're in the middle of a name change from "Lamar Games"),
> www.lamargames.net.
>
> Our first commercial game, Andromeda Online (www.andromedaonline.net) is
> going into beta soon. It runs on an evaluation edition of SQL Server 2000
> (our intention is, when it launches, we earn the money to buy a copy before
> the evaluation expires).
>
> We have been testing Andromeda Online, and found that saves to the database
> take about 10 seconds (we were anticipating less than 1). We felt we need
> somebody experienced in optimizing sql databases to help us optimize the
> database, and get it running in the best method for our particular
> application.

You need to find out what it is doing during these 10 seconds. Run
some kind of profiling on your progarm to see what it is doing. i.e
Opening JDBC connection (if it's doing that), getting a connection
from a connection pool (you really should be doing something like
that), sending sql statement to server, waiting for server to complete
the statement,closing (or releasing) the dabase connection. When you've found
out how much time these various things are taking then you can see which parts
you need to optimise.

> Our program accesses the database in Java, and people with understanding in
> how to optimize java connections would be a tremendous help.

Use pooling so that you don't have to open a connection every time you
want to access the database

If your problem is tha database then you'll more than likely get
improvements by looking at ths SQL you are using and seing if you can
rephrase your statement in a btter way (i.e. have a look at any joins
you are using and whic table is the driving table in a join, make sure
that any table lookups are using indexes etc. ) You'll fidn that any
optimistaion you make in this area will make much more of a difference
than any tweaking of database parameters that you can do.

>
> My company is small, and we honestly cant afford much. Everybody on this
> project, from the sound guys to the graphic artist, has worked for 1/10 to
> 1/100 of the value of the job. We're simply a starting company looking for
> dedicated people who are willing to work more for credit than money.

Good luck with your product, I hope it works out so that all the guys
who've been working for little money can start getting very lareg
pay-checks

> We can offer credit on our website
> (http://www.andromedaonline.net/credits.html) to anybody who helps us, but
> little more (maybe $100, but we're very over budget, and in desperate need
> of help). Because of how we intend the game to run (with maybe 100-200
> concurrent games running online), a 10 second save time is simply
> unacceptable.

You can have this info for nothing, enjoy :-).

Here's some more advice for free. If you've got nobody on your team
who knows about databases and their design your design is probably a
bit if a mess. It will then probably take a bit more than some tunig
to sort out your problems. Just have a look at posts in some of the
Oracle newsgroups to get some idea of what professional database
people think about letting java programmers loose on databases :-)

> Anybody who would be willing to help us, please send a resume to
> help@.andromedaonline.net. Experience would be nice, but not a requirement.
> We're looking for someone who can talk with our programmer about the types
> of calls made to our SQL database, and then can log into the DB and optimize
> it to run as fast as possible considering our specific needs.

As someone else posted , get one of the programmers to come on here, but
get him/her to read the above first and come ready with answers to the
questions above and then getting answers back will be a lot quicker,

cheers

Phil

--

As of now they're on Double SECRET Probation!|||Phil Britton <phil@.phil-britton.com> wrote in message news:<ulluryleo.fsf@.phil-britton.com>...
> "BlackHawke" <blackhawke@.legacygames.net> writes:
> > My Name is Nick Soutter, I am the owner of a small game company, Aepox Games
> > (We're in the middle of a name change from "Lamar Games"),
> > www.lamargames.net.
> > Our first commercial game, Andromeda Online (www.andromedaonline.net) is
> > going into beta soon. It runs on an evaluation edition of SQL Server 2000
> > (our intention is, when it launches, we earn the money to buy a copy before
> > the evaluation expires).
> > We have been testing Andromeda Online, and found that saves to the database
> > take about 10 seconds (we were anticipating less than 1). We felt we need
> > somebody experienced in optimizing sql databases to help us optimize the
> > database, and get it running in the best method for our particular
> > application.
> You need to find out what it is doing during these 10 seconds. Run
> some kind of profiling on your progarm to see what it is doing. i.e
> Opening JDBC connection (if it's doing that), getting a connection
> from a connection pool (you really should be doing something like
> that), sending sql statement to server, waiting for server to complete
> the statement,closing (or releasing) the dabase connection. When you've found
> out how much time these various things are taking then you can see which parts
> you need to optimise.
> > Our program accesses the database in Java, and people with understanding in
> > how to optimize java connections would be a tremendous help.
> Use pooling so that you don't have to open a connection every time you
> want to access the database
> If your problem is tha database then you'll more than likely get
> improvements by looking at ths SQL you are using and seing if you can
> rephrase your statement in a btter way (i.e. have a look at any joins
> you are using and whic table is the driving table in a join, make sure
> that any table lookups are using indexes etc. ) You'll fidn that any
> optimistaion you make in this area will make much more of a difference
> than any tweaking of database parameters that you can do.
> > My company is small, and we honestly cant afford much. Everybody on this
> > project, from the sound guys to the graphic artist, has worked for 1/10 to
> > 1/100 of the value of the job. We're simply a starting company looking for
> > dedicated people who are willing to work more for credit than money.
> Good luck with your product, I hope it works out so that all the guys
> who've been working for little money can start getting very lareg
> pay-checks
>
> > We can offer credit on our website
> > (http://www.andromedaonline.net/credits.html) to anybody who helps us, but
> > little more (maybe $100, but we're very over budget, and in desperate need
> > of help). Because of how we intend the game to run (with maybe 100-200
> > concurrent games running online), a 10 second save time is simply
> > unacceptable.
> You can have this info for nothing, enjoy :-).
> Here's some more advice for free. If you've got nobody on your team
> who knows about databases and their design your design is probably a
> bit if a mess. It will then probably take a bit more than some tunig
> to sort out your problems. Just have a look at posts in some of the
> Oracle newsgroups to get some idea of what professional database
> people think about letting java programmers loose on databases :-)
>
> > Anybody who would be willing to help us, please send a resume to
> > help@.andromedaonline.net. Experience would be nice, but not a requirement.
> > We're looking for someone who can talk with our programmer about the types
> > of calls made to our SQL database, and then can log into the DB and optimize
> > it to run as fast as possible considering our specific needs.
> As someone else posted , get one of the programmers to come on here, but
> get him/her to read the above first and come ready with answers to the
> questions above and then getting answers back will be a lot quicker,
> cheers
> Phil

Hi

Have noted what you guys are trying to do, and as a DBA and
ex-developer, thought I'd try and help point you in the right
direction. From the sounds of what you are doing and your tight
budget, I guess you're not running the SQL DB on a powerful,
multi-processor, multi-GB RAM, multi-SCSI Array system. If this is
the case and you're system is modest, then there isn't a great deal
you can do for tuning the SQL Server itself.

As someone mentioned earlier, you're more likely to get better
performance by looking at the way you do your SQL coding - think there
is a quote in the O'Reilly T-SQL Programming book, that says
"...optimising SQL Server settings only accounts for around 20% of
performance improvement, 80% is obtained by tuning your SQL code...".
Do you make use of Stored Procedures? This will help, as compared to
using SQL code from the Client. Don't use SQL Server Cursors - make
sure your code is Set-based (what RDBMS's are designed to run best
with). Look into the Metadata structure - are there loads of joins?
If so, consider denormalising and test to see if this gives an
improvement. Is it the SQL Server that is slow to update? or is it
the connection/network link? You might find that the actual writing
to the DB is quick and the bottleneck is elsewhere.

HTH

n0t999