SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Joins Accross Servers and Time Outs
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Heath
Starting Member

USA
3 Posts

Posted - 11/28/2006 :  12:20:34  Show Profile  Send Heath an AOL message  Send Heath an ICQ Message  Click to see Heath's MSN Messenger address  Send Heath a Yahoo! Message  Reply with Quote
First thanks for your time.

We have 4 clustered SQL2000 Servers each contains information specific to its application related to customer information in a casino player tracking database. My problem is as follows On the Playertracking database I can join and return information from the tables there with no problems the performance accross the decently sizable transactional based table is pretty decent. The problem is I need to filter this query down by the Type of machine the customer plays. The child key exists in the playertransaction table the parent key is on another server. Here is the lay out of the tables unecessary information from the tables were truncated for brevity.

CREATE TABLE [dbo].[PlayerSession] (
[PlayerId] [int] NOT NULL ,
[Mnum] [int] NOT NULL ,
[CoinIn] [money] NOT NULL ,
[CoinOut] [money] NOT NULL ,
[Games] [int] NOT NULL ,
[Jackpot] [money] NULL ,
[Win] [money] NULL ,
[TheoWin] [money] NOT NULL ,
[PlayerMod] [tinyint] NOT NULL
) ON [PRIMARY]

-- Player Demographics information
CREATE TABLE [dbo].[Player] (
[PlayerId] [int] NOT NULL ,
[Status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Title] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FirstName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[MiddleName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LastName] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SSN] [varchar] (16) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]

--Machine Information that links to Machine Type table

CREATE TABLE [dbo].[Machine] (
[MNum] [int] NOT NULL ,
[MachineTypeId] [smallint] NOT NULL ,
) ON [PRIMARY]

-- Machine type code table

CREATE TABLE [dbo].[MachineType] (
[MachineTypeId] [smallint] NOT NULL ,
[Denom] [int] NOT NULL ,
[Par] [decimal](6, 2) NOT NULL ,
[GameType] [varchar] (40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
) ON [PRIMARY]

From the server where all the player information is ran I can query the linked database for the machine and machine type information like this.

SELECT m.MNum, mt.MachineTypeId, mt.GameType, mt.DisplayType
FROM ACCTV.Accounting.dbo.Machine m INNER JOIN
ACCTV.Accounting.dbo.MachineType mt ON m.MachineTypeId = mt.MachineTypeId

This is the information I am trying to get out but the query times out on me.

SELECT Player.PlayerId, Player.FirstName, Player.LastName, SUM(PlayerSession.CoinIn) AS sumCI, SUM(PlayerSession.CoinOut) AS SumCO,SUM(PlayerSession.TheoWin) AS SumTheo, AVG(PlayerSession.TheoWin) AS AvgTheo, SUM(PlayerSession.Win) AS SumWin, AVG(PlayerSession.Win) AS AvgWin, mt.GameType
FROM Player INNER JOIN
PlayerSession ON Player.PlayerId = PlayerSession.PlayerId INNER JOIN
ACCT.Accounting.dbo.Machine M ON PlayerSession.Mnum = M.MNum INNER JOIN
ACCT.Accounting.dbo.MachineType mt ON M.MachineTypeId = mt.MachineTypeId
GROUP BY Player.PlayerId, Player.FirstName, Player.LastName, mt.GameType

The other option would be some sort of SubQuery but I dont know how to return results from the subqueries to the root query to be returned to the restulting recordset. I am not necessarily looking for an answer more of looking for a direction to go to find my solution.

Thanks again for your help.

Heath
Starting Member

USA
3 Posts

Posted - 11/28/2006 :  12:31:00  Show Profile  Send Heath an AOL message  Send Heath an ICQ Message  Click to see Heath's MSN Messenger address  Send Heath a Yahoo! Message  Reply with Quote
I think the problem is Partly the remote query and the other part of the problem is the group by is on a table with 10 million rows

Players 250k Rows
Playersession 10m rows
machine and machine type are ~2500
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000