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.
Author |
Topic |
Heath
Starting Member
3 Posts |
Posted - 2006-11-28 : 12:20:34
|
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 informationCREATE 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 tableCREATE TABLE [dbo].[Machine] ( [MNum] [int] NOT NULL , [MachineTypeId] [smallint] NOT NULL ,) ON [PRIMARY]-- Machine type code tableCREATE 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.DisplayTypeFROM ACCTV.Accounting.dbo.Machine m INNER JOIN ACCTV.Accounting.dbo.MachineType mt ON m.MachineTypeId = mt.MachineTypeIdThis 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.GameTypeFROM 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.MachineTypeIdGROUP BY Player.PlayerId, Player.FirstName, Player.LastName, mt.GameTypeThe 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
3 Posts |
Posted - 2006-11-28 : 12:31:00
|
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 rowsPlayers 250k RowsPlayersession 10m rowsmachine and machine type are ~2500 |
|
|
|
|
|
|
|