Efficient Way To Store Game Data: Help?

Jun 22, 2007

Hello everyone!

So I am trying to wrap my head around the most efficient way to store some game data in sql server 2005.

I wont list all of my tables or columns as there would just be too many, but will try to post as exact as I can.

Basically lets say I have 3000 monsters in my game, and over 8000 items some of which are monster loot.

Each monster could have 1 drop item or they could have 200 different item drops all depending on the monster.

So I am just trying to find the best method of creating a loot table.

So I have the following in mind.

[MonsterTable]

monsterID;

monsterName;

etc...

[ItemTable]

itemID;

itemName;

etc..

[LootTable]

lootTableID;

refMonsterID;

refItemID;

So I'm not worried about how I would get all of the data in there, I'm just wondering if this simple method of using Ref. ID's between tables would provide the best method for extracting the data when needed.

Any ideas or thoughts are more then welcome, any questions just ask, and thanks for any guidance or information you can provide on a more efficient manner.

View 1 Replies

Most Efficient Way Of Returning Data

Feb 22, 2007

I have a number of tables, and I need to create a summary of the data.Now I have two choices. I could create a stored procedure that creates a temp table with the filters applied. This would require 12 selects for each year (we have 3 years of data so far). This means 36 selects so far that fill a temp table.The second choice is to create a table with the required columns and rows. As I am esspecially cross-joining 4 tables, the table would have about 10 x 10 x 12 x A rows, where A is a variable that will grow quickly. I can then keep this table up-to-date using triggers for each insert. Then all I need to do is use an aggregate funtion on the relevant filter.My question is, which is more efficient. Creating a stored procedure that creates the table dynamically, or have a table with thousands of rows and using an aggregate function on these.PS I am using SQL Server 2000Jag

View 1 Replies View Related

Efficient Way Of Backing Up Data

Jan 24, 2008

We are writing an in-house backup tools. It's written in C# and the program allows me to select which records we want to archive and remove from existing database. Functionally, we can acheive what we want to do but I feel that we did not do it right. Can someone show me the right way to go? Here is what we are doing in the backup process.

1) Create an archive database (with the file name, say archive_101.mdf)
2) Move the data from orginal database to archive database using the following SQL statement
INSERT INTO [archive_database].dbo.records SELECT a, b, c, date FROM [original_database] WHERE date < CONVERT(DATETIME, '2007-10-10', 120)
3) Delete the moved data from original database.
4) Of course, step 2 and step3 are done in a transaction
5) Then, we detach the archive database and store the file "archive_101.mdf somewhere.

I felt it's wrong because the performance is very bad. If I run the select statement, it takes me 1 min to dump all the data to console. If I run the same SELECT statement together with INSERT INTO, like what I wrote in step 2, it takes me more than 1 hour to write to another database. I checked the tempdb and its size does grow a lot when I am doing step 2. I know the data I am selecting is about 500MB large but still, it should not take that long. Can somebody give me any hints?

Thanks in advance.

View 5 Replies View Related

Are Data Sets Memory Efficient?

Feb 8, 2008

Hi,

I have designed a contact manager with Data Grid Control bound to a Data Set.

When the application closes, data from Data Set is written to XML file and when application opens, data from XML file is loded into Data Set and is show in Data Grid control.

Contacts in my application can exceed over 1,000
So, Is Data Set capable of handling lot of data very efficiently in memory?

Please advise

View 3 Replies View Related

SQL Game

Jul 20, 2005

Here's a checkers game written in T-SQL that you can play in the sqlquery analyzer.http://www.geocities.com/swint002/checkers.txtEnjoy :-)

View 3 Replies View Related

Doing A Data Import Using DTS Wizard In SQL Server 2005 - Being Efficient With 5 Flat Files

Apr 13, 2006

Hi,

I'm a new user of SQL Server 2005. I have the full version installed. I also have SQL Server Business Integration Dev Studio installed. My OS is Windows XP.

I'm importing a series of 5 flat files into a database on one of the SQL Servers we have. My goal is to get 5 different tables (though perhaps I should do one and add an extra field to distinguish each import) into the database for further analysis.

I tried doing an import via DTS Wizard. There are no column names in the flat file so I defined them during the import process (all 58 of them). When I got to the end, I had an option to save the import process as a SSIS (SQL Server Integration Service) Package on:

SQL SERVER (I don't have permission for this)

or

FILE SYSTEM (did this one)

I saved the Package locally in hopes of being able to go back in, change the source file and destination table of the package and quickly get the other 4 flat files imported.

My problems are:

1) I couldn't find how to run the *.DTSX Package file to run in SQL Server Studio (basically reuse the Package with minor changes and saving me having to redefine the same 58 columns on each flat file import)

2) Tried but didn't understand how to run it in SQL Server Bus Intel Dev Studio (i.e. understanding the mapping and getting the data types right so it wouldn't error out)

3) Don't know how to make the necessary changes so that the Package handles the next source file and puts in a new destination table (do I need to do 5 CREATE TABLES so this Package has a place to run to?)

4) Does the Package need to be part of a Project to run (I haven't found how to take an existing Package and make it part of a Project/Solution)?

5) Is there a good book or online resource for just getting the basics of using SQL Server 2005 and SQL Server Business Intelligence Development Studio?

I'm really at a loss after spending a day fruitlessly on it scouring the help files, forums and experimenting around.

Hope somebody can point me in the right direction.

Regards,

Patrick Briggs,
Pasadena, CA

View 7 Replies View Related

Doing A Data Import Using DTS Wizard In SQL Server 2005 - Being Efficient With 5 Flat Files

Apr 18, 2006

I just spent some time working out how to do a seemingly simple task. I��m sharing the steps I took to do this in hopes it saves other SQL Server 2005 users (especially newbies like myself) time.

My original question posed on several SQL newsgroups was based on this goal:

I'm importing a series of 5 flat files (all with same file layout) into a database on one of the SQL Servers we have using SQL Server 2005 (SQL Server Management Studio) . My goal is to get 5 different tables. I want to do this without having to redo all the layout criteria 4 additional times.

Below are the steps I followed to get a solution (all done in Microsoft SQL Server Management Studio):

Create the Package (data import)

1) Use the SQL Server Import Export Wizard (equivalent to SQL Server 2000 Data Transfer Wizard) to import your first flat file. At the CHOOSE DATA SOURCE window browse for your file.
2) Under the Advanced tab, you can set your Column attributes (��output column width�? or ��data type�? to name a few). I highlighted all the columns and selected ��string [DT_STR]�? for data type. To avoid truncation errors, I selected 255 for output column width. You can name the columns whose data you are most concerned with (I did import all the available fields).
3) After choosing a server destination you will have a ��SELECT SOURCE TABLES AND VIEWS�? window pop up. Under the ��Mapping�? column you can choose to tweak your mapping further editing in SQL (see Edit SQL button). I didn��t.
4) The ��SAVE AND EXECUTE PACKAGE�? will pop up. The ��Execute Immediately�? box should be checked and you should check the ��Save SSIS Package�? (SQL Server Integration Services). When you do, select ��File System�? for where to save this import-file-package to.
5) Click OKAY for the Package Protection Level and the ��SAVE SSIS PACKAGE�? window will appear. Browse for a path on your local computer to save to.

Modify Package (data import) for Next Use

6) In SQL Server Management Studio, browse for the Package and open it.

Preparation for SQL Task �� box

7) You should see a screen that shows two boxes (��Preparation for SQL Task�?) and (��Data Flow Task�?).
8) Right click on the former and select ��Edit�?.
9) On the ��SQL Statement�? row, click into the right column and select the ��? box
10) Change the destination table (the table you will create with this package) to a meaningful name and click OK.
11) Click OK for the ��SQL Task Editor�?

Data Flow Task - box

12) Right click on the ��Data Flow Task�? box and select ��Edit�?.
13) Three boxes will appear ��SourceConnectionFlatFile�?, ��Data Conversion 1�?, and ��Destination - <whatever table name your original data import went to>�?. Below them is a section that displays ��Connection Managers�?

SourceConnectionFlatFile - editing

14) The first thing you will want to do is change the import source to a new flat file. You do this by going below the boxes under the ��Connection Managers�? window and right clicking on ��SourceConnectionFlatFile�? and then selecting ��Edit�?
15) Browse for the new ��File Name�? and select it.
16) A ��Microsoft SQL Server Management Studio�? window will pop up asking you if you want to ��keep or reset the existing metadata�?. The metadata is just your column definitions and choosing ��YES�? to keep this makes sense if you are doing data imports on files with the same file layout.
17) Still in the ��Flat File Connection Manager Editor�? window, change the ��Connection Manager Name�? to something meaningful (I add <_> at the end and then the name of the table the flat file is going to) and click OK.

SourceConnectionFlatFile �� box (editing)

18) Right click on the ��SourceConnectionFlatFile�? box and select ��Edit�?.
19) Your newly named ��Flat File Connection Manager�? should appear in select box.
20) Click OK, right click again on the ��SourceConnectionFlatFile�? box and select ��Show Advanced Editor�?.
21) Under the ��Connections Manager�? tab, your newly named ��Flat File Connection�? should appear (the prior step is necessary for the advanced editor to recognize your change).
22) Under the ��Component Properties�? tab, on the ��Name�? row, click into the right column and rename to something meaningful (notice the ��Identification String�? row description changes too once you click out of the ��Name�? row)
23) Under the ��Column Mappings�? tab, just confirm you are mapping your flat file fields (��Available External Columns�?) to a destination table��s fields (��Available Output Columns�?).
24) Under the ��Input and Output Properties�? tab you can check in ��Flat File Source Output�? to make modifications to either your ��External Columns�? or your ��Output Columns�? �� you shouldn��t need to for a simple import.
((NOTE: any changes you make here would likely need to be consistent with the column properties found under the ��Connection Manager Window�? for the ��SourceConnectionFlatFile�? as well as the ��Data Conversion 1�? box under the ��Data Flow Tasks�? window, so exercise caution
25) NOTE: This process has worked for me by making my source columns all ��string [DT_STR]�? data type and the output columns all ��Unicode String [DT_WSTR]�? data type.

Data Conversion 1 �� box (editing)

26) There is nothing you need to do here. By right clicking on the ��Data Conversion 1�? box and selecting ��Edit�?, you can see and change the data type of the output columns (the ones in the table your importing the flat file to). There are probably more edits one can do but they��re beyond what I��ve learned.

Destination - <whatever table name your original data import went to> �� box (editing)

27) Right click on the ��Destination - <whatever table name your original data import went to>�? box and select ��Show Advanced Editor�?.
28) Select the ��Component Properties�? tab.
29) Select the right column at the ��Name�? row and change the name to something meaningful (ie. related to the source file name or the table name you��re importing to).
30) Select the right column at the ��Identification String�? row and it will update to this change.
31) Select the right column at the ��OpenRowSet�? and change it to the name of the table you are importing your flat file to (this should be consistent with table name under step 10).
32) Click OK
33) Select FILE and select ��Save As��? and then give your package a new name that��s meaningful (this will be helpful if you have to rerun the import of the flat file later).

Run (execute) the Revised Package (data import)

34) Go back to SQL Server Management Studio and open the Object Explorer
35) Connect to an ��Integration Services�? component. This should essentially be a local instance (not sure where it is on the local computer or in SQL Server Management Studio on the local computer).
36) In ��Object Explorer�? go down to your ��Integration Services�? object and expand it.
37) Expand ��Stored Packages�?
38) Right click on ��File System�? and select ��Import Package�? and an ��IMPORT PACKAGE�? window will appear
39) For ��Package Location�? choose ��File System�? and then browse for the ��Package Path�?
40) Click into the ��Package Name�? and it defaults to your Package��s file name.
41) Click OK and the Package is imported.
42) Right click on the newly imported Package and select ��Run Package�?
43) An ��Execute Package Utility�? window appears
44) Select ��Execute�? and the package runs.

View 1 Replies View Related

Update Game Schedule

Apr 1, 2015

Before the start of each season, the various clubs/teams compile an initial game schedule using Excel. Once the master schedule is complete, it is sent to me. The initial master schedule is loaded into the database [MatchSchedule] from the Excel spreadsheet using the SQL Server Import/Export Wizard. This works well enough. However, during the season there are frequent small changes to dates & times, venues, etc. These changes are sent to me in Excel spreadsheets.

I need an efficient & ERROR FREE method of getting the new game details into the master schedule. I thought perhaps the easiest way to accomplish updates to the table MatchSchedule t1 was to use a temporary table MatchSchedule_Updates_temp t2 to hold the new game details (obtained from Excel spreadsheets) and then use UPDATE to update MatchSchedule with those games that have changed. The primary key is Match_Id.

Unfortunately, I keep getting the following error messages which I have been unable to correct!

Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 't1'.
Msg 156, Level 15, State 1, Line 5
Incorrect syntax near the keyword 'SET'.
Msg 156, Level 15, State 1, Line 41
Incorrect syntax near the keyword 'WHERE'.

Here is my code:-

USE [database]
GO
UPDATE dbo.MatchSchedule t1
SET (
t1.MatchDate
,t1.MatchTime
,t1.Venue

[code]....

View 2 Replies View Related

A Game Company In Trouble

Jul 20, 2005

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) isgoing 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 beforethe evaluation expires).We have been testing Andromeda Online, and found that saves to the databasetake about 10 seconds (we were anticipating less than 1). We felt we needsomebody experienced in optimizing sql databases to help us optimize thedatabase, and get it running in the best method for our particularapplication.Our program accesses the database in Java, and people with understanding inhow to optimize java connections would be a tremendous help.My company is small, and we honestly cant afford much. Everybody on thisproject, from the sound guys to the graphic artist, has worked for 1/10 to1/100 of the value of the job. We're simply a starting company looking fordedicated 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, butlittle more (maybe $100, but we're very over budget, and in desperate needof help). Because of how we intend the game to run (with maybe 100-200concurrent games running online), a 10 second save time is simplyunacceptable.Anybody who would be willing to help us, please send a resume toJoin Bytes!. Experience would be nice, but not a requirement.We're looking for someone who can talk with our programmer about the typesof calls made to our SQL database, and then can log into the DB and optimizeit to run as fast as possible considering our specific needs.Thank you for your time.Nick SoutterAepox (Lamar) Games

View 14 Replies View Related

Mising Dll To Play A Pc Game

Jul 30, 2007

hi cant i receive de direct page to downloads or upgrade de d3dx9_29.dll

View 1 Replies View Related

Creating A DB To Query Game Server

Mar 3, 2008

Im just wondering if it is possible to use a SQL DB to query game servers, Im very new to SQL, an if it is possible please lead me in the right direction on start this.

Thanks,

View 8 Replies View Related

Openings In An Online Game Company For Sql Coders

Jul 23, 2005

Those who is interested in computers, games can check out a freemassive online strategy scifi game at http://www.spacefederation.netThis game is also looking for staff, especially those in the computerline like programming, development, it and Internet security. to apply,goto the game and click on jobs, or click on contact to send them anemail to apply. they are always on the lookout for talented andpassionate people, if there is no such post available they will makeone for you.Cowly the Game playerVisit some of the games I playhttp://www.gamestotal.comhttp://mmorpg.gamestotal.comhttp://www.gamestotal.com/news/http://free_mmorpg.gamestotal.comhttp://strategy_games.gamestotal.comhttp://www.gamestotal.com/strategygames/http://free_strategy_games.gamestotal.com

View 1 Replies View Related

Asp.net Page Is Unable To Retrieve The Right Data Calling The Store Procedure From The Dataset/data Adapter

Apr 11, 2007

I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?

View 6 Replies View Related

How Can I Store Data ?(encoding And Data Type)

Oct 20, 2007

hi my friends;i have got a problem.i thing you can help mea='x80x02}qx00(Kx02Kx03Kx04Kx06Kx05Kx07u. 'i want to store database but i don't know which encoding and dataytpepls help mesorry for my bed english...thanks all

View 3 Replies View Related

Integration Services :: Purge Data In Transaction Table Or Delete Some Data And Store In Separate Table

Aug 18, 2015

How to purge data in transaction table or we can delete some data and store in separate table in data warehouse?

View 7 Replies View Related

Store All Data In One Coloumn ?

Dec 31, 2006

we need to decide an architecture for Performance on a web site Search! I wanna use text service of SQL 2005 .But I am worried about the performance .... How should I design the system if I want the best perfomance and scalability ?1.Should I build a seperate coloumn in my every table and merge all the information into one coloumn and full text index that column.2.Put a full text index in all column in the table and use OR clause and reverse rank it for AND clause,using CONTAINSTABLE function.3.Make a different table and put _ID,_TYPE and _VALUE fields and search in that table with less coloumns.4.Seperate the full text database and search in a seperate db so that I can scale better?did anybody have a similiar problem ? Any books on full text search ?

View 4 Replies View Related

How Can I Store Data In One Row Instead Of 10 Rows?

Jan 1, 2007

Hi
I have this stored procedure using Table Variable.
-------------------------------------------------------------------------------------------------------------------------------
create procedure sp_matching @pid Int
ASdeclare @tbl table (MAID int, image varchar(100))
insert @tblselect MAID, image from productswhere pid in (select top 10 mid from matching1 where pid=@pid order by newid())
select * from @tbl
GO
---------------------------------------------------------------------------------------------------------------------------------
When I run this sp, i have table with 10 rows. But what I really need to do is to store all data in one row like:
PID, MAID1, Image1, MAID2, Image2, ..... MAID9, Image9, MAID10, Image10

How can I do this? Please help me out for this.
Thanks.

View 5 Replies View Related

How To Store HTML Data In SQL

Aug 19, 2007

Hi friends I want to add HTML to the SQL server as data please can anyone help me. which data type . & how to store the data. Thanks for all your support Thanks & regardsASPFreak

View 3 Replies View Related

How Data Store On Server??

Feb 15, 2004

Hi all,
please, show me How to data is store on the server ?
thanks for reading.

View 5 Replies View Related

ETL: OLTP -&> DATA Store

Jul 23, 2005

Greetings All, I was wondering if any of you would share some of yourexperiences regarding the task of loading a data store from an Oltpsource. We are using Analysis Services in a BI product that requiresdata to be pulled from one of our products, an OLTP database. Thedesign is to first run an ETL process from the OLTP source into anoperational data store, from here Analysis Services will pull its datato do its thing. Now, for small OLTP databases (< 1Gb) the storedprocs I have written to do the extraction works well, it is relativelyfast and efficient. However, we have a few databases that are 10Gb'sand the load could end up taking several hours. During this long loadthe OLTP source may be in use and I want to avoid write blocks, or if Iwere to use "select ... NOLOCK" I could get dirty data brought over. Icould used BCP for some of the big tables or Bulk Copy but I wanted tosee if anyone has dealt with this issue and what their specificresolution was for their specific problem. It is my hope that byseeing how others have dealt with this I will be able to architect asolution for my specific problemRegards, TFD.

View 1 Replies View Related

Where Are BLOB Or VAR**(MAX) Data Store?

Apr 7, 2007

hi,

i learned that BLOB data types are out-row data by default, i think it means it will not store in the row of table, because the max-length of a col is 8KB.

but will the system store those BLOB data? and is there any way to specify where should it be stored, like tha Partition Schema.

i am using SQL Express, so i hope it works for express.(partition does not work for express)

View 5 Replies View Related

Most Efficient DataSource?

Aug 3, 2007

Hi there,
I'm using a Repeater at the moment which is bound to a SQLDataSource. I expect much load on that Website, should I choose another DataSource? Which other DataSource is better if it's about Performance?
I read some stuff about the SQLAdapter and a DataSet.. is that better in performance? Why is it better?
What about LinQ?
Thanks a lot for any clarification.

View 3 Replies View Related

More Efficient Code

Dec 13, 2007

Hi all, I have the code listed below and feel that it could be run much more efficiently. I run this same code for attrib2, 3, description, etc for a total of 21, so on each postback I am running a total of 21 different connections, i have listed only 3 of them here for the general idea. I run this same code for update and for insert, so 21 times for each of them as well. In fact if someone is adding a customer, after they hit the new customer button, it first runs 21 inserts of blanks for each field, then runs 21 updates for anything they put in fields, on the same records. This is running too slow... any ideas on how I can combine these?? We have 21 different entries for EVERY customer. The Pf_property does not change, it is 21 different set entries, the only one that changes is the Pf_Value.
Try                Dim queryString As String = "select Pf_Value from CustomerPOFlexField where [Pf_property] = 'Attrib1' and [Pf_CustomerNo] = @CustomerNo"                Dim connection As New SqlClient.SqlConnection("connectionstring")                Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(queryString, connection)                command.Parameters.AddWithValue("@CustomerNo", DropDownlist1.SelectedValue)                Dim reader As SqlClient.SqlDataReader                command.Connection.Open()                reader = command.ExecuteReader                reader.Read()                TextBox2.Text = Convert.ToString(reader("Pf_Value"))                command.Connection.Close()            Catch ex As SystemException                Response.Write(ex.ToString)            End Try
            Try                Dim queryString As String = "select Pf_Value from CustomerPOFlexField where [Pf_property] = 'Attrib1Regex' and [Pf_CustomerNo] = @CustomerNo"                Dim connection As New SqlClient.SqlConnection("connectionstring")                Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(queryString, connection)                command.Parameters.AddWithValue("@CustomerNo", DropDownlist1.SelectedValue)                Dim reader As SqlClient.SqlDataReader                command.Connection.Open()                reader = command.ExecuteReader                reader.Read()                TextBox5.Text = Convert.ToString(reader("Pf_Value"))                command.Connection.Close()            Catch ex As SystemException                Response.Write(ex.ToString)            End Try
            Try                Dim queryString As String = "select Pf_Value from CustomerPOFlexField where [Pf_property] = 'Attrib1ValMessage' and [Pf_CustomerNo] = @CustomerNo"                Dim connection As New SqlClient.SqlConnection("connectionstring")                Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand(queryString, connection)                command.Parameters.AddWithValue("@CustomerNo", DropDownlist1.SelectedValue)                Dim reader As SqlClient.SqlDataReader                command.Connection.Open()                reader = command.ExecuteReader                reader.Read()                TextBox6.Text = Convert.ToString(reader("Pf_Value"))                command.Connection.Close()            Catch ex As SystemException                Response.Write(ex.ToString)            End Try
Thanks,
Randy

View 2 Replies View Related

Which Query Is More Efficient?

Apr 1, 2008

what's the difference, if I use SQLDataReader at code level, making a query of that retrieves 500 rows and 2 columns, and making a query that retrieves 2 rows and 500 columns?

View 6 Replies View Related

Most Efficient Way To Do This Select....

Feb 12, 2002

I have a table that has the following...

ID Status Type Check_Num Issued IssueTime Paid PaidTime
-----------------------------------------------------------------
1 I <null> 10 10.00 2/1/02
2 E IDA 10 <null> <null> 10.01 2/3/02
3 E CAP 10 <null> <null> 10.00 2/4/02
4 E PNI 11 <null> <null> 15.00 2/6/02

I want to return the Check_Num,Type, Paid, and Max(PaidTime) from this...

Example:
Check_Num Type Paid Time
---------------------------
10 CAP 10.00 2/4/02
11 PNI 15.00 2/6/02

Any assistance will be greatly appreciated.

Thanks,
Brian

View 1 Replies View Related

In-efficient SQL Code

Sep 7, 2000

Hey people

I'd be really grateful if someone can help me with this. Could someone explain the following:
If the following code is executed, it runs instantly:

declare @SellItemID numeric (8,0)
select @SellItemID = 5296979

SELECT distinct s.sell_itm_id
FROM stor_sell_itm s
WHERE (s.sell_itm_id = @SellItemID )

However, if I use this WHERE clause instead -

WHERE (@SellItemID = 0 OR s.sell_itm_id = @SellItemID)

- it takes 70 micro seconds. When I join a few more tables into the statement, the difference is 4 seconds!

This is an example of a technique I'm using in loads of places - I only want the statement to return all records if the filter is zero, otherwise the matching record only. I think that by using checking the value of the variable in the WHERE clause, a table scan is used instead of an index. This seems nonsensical since the variable is effectively a constant. Wrapping the entire select statement with an IF or CASE works, but when I've got 10 filters I'd have to 100 select statements.
I DON'T GET IT!! There must be a simple answer, HELP!!
Jo

PS this problem seems to occur both in 6.5 and 7.0

View 4 Replies View Related

Need Efficient Query

Jun 12, 2008

This query is giving me very slow search .What could be the efficient way

SELECT (
SELECT COUNT(applicationID) FROM Vw_rptBranchOffice
WHERE ( statusDate between '2008-03-13 16:12:11.513' AND '2008-05-30 00:00:00.000'
AND SearchString like '%del%')) AS
totalNO,ApplicationID,SearchString,StudentName,IntakeID,CounslrStatusDate
FROM Vw_rptBranchOffice
WHERE statusDate between '2008-03-13 16:12:11.513' AND '2008-05-30 00:00:00.000' AND
SearchString like '%del%'

View 5 Replies View Related

More Efficient Way Than You Have Published

Dec 8, 2004

Sumanesh writes "Recently I read one article “Converting Multiple Rows into a CSV string�?

(http://www.sqlteam.com/item.asp?ItemID=256)

I have found one easy and more efficient way to achieve the same thing.
First I have a table called test with 2 columns (ID and Data)
And some data inserted into it.

CREATE FUNCTION [dbo].CombineData(@ID SMALLINT)
RETURNS VARCHAR(2000)
AS
BEGIN
DECLARE @Data VARCHAR(2000)

SET @Data = ''
SELECT @Data = @Data + Data + ',' FROM Test WHERE ID = @ID
RETURN LEFT(@Data,LEN(@Data)-1)

END
GO

Then used a simple select query to achieve the same

SELECT DISTINCT ID, [dbo].CombineData(ID) FROM Test

Which achieved the same thing without using any temporary tables and Cursors. I am sure that you will accept that this is more efficient than the one that has been published.

Thanks
Sumanesh"

View 1 Replies View Related

Efficient SQL Backup?

Apr 19, 2006

Hi all,I am having issues of efficiency of backing up data from one SQL database to another.The two servers in questions are on different networks , behinddifferent firewalls. We have MS SQL 2000.On the source data i run a job with the following steps:1> take trans backup every 4 hrs2> ftp to the remote server3> if ftp fails , disable the whole jobOn the target server I run a job which does the following1> restore the trans backup with NORECOVERY.If the job fails at target. I will have to go through the whole processof doing a complete backup of the source , restoring it at the otherens and then starting trans-backup again.Also, if we do a failover to the target server, then when we roll backto the source server again we have to da a back-up of the target andrestore it on the source server.Is ther a more efficent way of doing this??

View 3 Replies View Related

More Efficient - Exists Or In

Jul 20, 2005

Which is more efficient:Select * from table1 where id in (select id from table2)orSelect * from table1 where exists(select * from table2 wheretable2.id=table1.id)

View 8 Replies View Related

Efficient Select

Jul 20, 2005

It seems I should be able to do 1 select and both return thatrecordset and be able to set a variable from that recordset.eg.Declare @refid intSelect t.* from mytable as t --return the recordsetSet @refid = t.refidthe above doesn't work-how can I do it without making a second trip tothe database?Thanks,Rick

View 3 Replies View Related

Efficient Way Than IN Statement

Aug 21, 2006

I have two tables JDECurrencyRates and JDE Currency Conversion

I want to insert all the records all the records from JDECurrencyRates to JDECurrencyConversion that does not exists in JDECurrencyConversion table. For matching I am to use three keys i.e. FromCurrency, TO Currency and Effdate

To achieve this task i wrote the following query

INSERT INTO PresentationEurope.dbo.JDECurrencyConversion(Date,FromCurrency,FromCurrencyDesc,

ToCurrency, ToCurrencyDesc, EffDate, FromExchRate, ToExchRate,CreationDatetime

,ChangeDatetime)

(SELECT effdate as date, FromCurrency, FromCurrencyDesc, ToCurrency, ToCurrencyDesc, EffDate,

FromExchRate, ToExchRate, GETDATE(),GETDATE() FROM MAINTENANCE.DBO.JDECURRENCYRATES

WHERE FROMCURRENCY NOT IN (SELECT FromCurrency FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION)

OR TOCURRENCY NOT IN (SELECT TOCURRENCY FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION)

OR EFFDATE NOT IN (SELECT EFFDATE FROM PRESENTATIONEUROPE.DBO.JDECURRENCYCONVERSION))

Can any one suggest me the better way to accomplish this task or this query is OK (or efficient enough)

View 5 Replies View Related

Inserting Data From A Store Procedure

Oct 3, 2007

Hi,
This is more store procedure:ALTER PROCEDURE dbo.InsertSpecialOrders
(@OrderID int,
@DepotName nvarchar(50),@CustomerName nvarchar(50),
@OrderDate nvarchar(50),@TelephoneNumber nvarchar(50),
@ObtainedFrom nvarchar(50),@CustomerCanCollect nvarchar(50),
@DepositPaid nvarchar(50),@PriceQuoted nvarchar(50),
@OrderTakenBy nvarchar(50),@BalancePaid nvarchar(50),
@Status nvarchar(50),@TransferedBy nvarchar(50),@CarriagePrice nvarchar(50)
)

AS
Declare @LatestID Int
INSERT INTO Specials
(
OrderID,
DepotName,
CustomerName,
OrderDate,
TelephoneNumber,
ObtainedFrom,
CustomerCanCollect,
DepositPaid,
PriceQuoted,
OrderTakenBy,
BalancePaid,
Status,
TransferedBy,
CarriagePrice

)
VALUES
(
@OrderID,
@DepotName,
@CustomerName,
@OrderDate,
@TelephoneNumber,
@ObtainedFrom,
@CustomerCanCollect,
@DepositPaid,
@PriceQuoted,
@OrderTakenBy,
@BalancePaid,
@Status,
@TransferedBy,
@CarriagePrice
)
SELECT @LatestID = Scope_Identity()
INSERT INTO SpecialParts
(
OrderID,
PartNo,
Quantity
)
VALUES
(
@LatestID,
@DepotName,
@CustomerName

)
RETURN

Now I want to insert data and create the tables:
SqlDataSource ordersDataSource = new SqlDataSource();ordersDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["SpecialsConnectionString1"].ToString();

ordersDataSource.InsertCommandType = SqlDataSourceCommandType.StoredProcedure;
So to insert data for the first field I need:
ordersDataSource.InsertParameters.Add("@CustomerName", CustomerName.Text) Is this right? Any help is appreciated.

View 2 Replies View Related