sql

How to optimize indexes for a busy table?

i have this table of comments (little over 1 milion rows) that gets around 10.000 inserts and around 100.000 queries against it every day, and minor deletions and updates. the query that gets the comments causes performance issues that sometimes it locks up entire database and i am getting a lot of timeouts. please help me adjust my inde...

Faking SQL Server

I have application that requires SQL Server 2000 as database storage. I do not really want to use SQL Server 2000, but I can user MySQL Server instead. Application uses ODBC to connect to SQL Server Database. I would like to know if it is possible to make fake SQL Server which will send and receive data to/from MySQL Server applicatio...

What are the tradeoffs of reusing a cursor vs. creating a new cursor?

In cx_Oracle (or Oracle in general), is it possible to allocate a cursor for each query, or to reuse a cursor across several queries. def getSomeData(curs): # case 1: pass in a cursor, which is generally curs.execute('select ...') # reused across queries return curs.fetchall() def getSomeData(conn): # ca...

SQL Server Geography datatype nearest point on line

I am trying to build a query, but I am having some difficulty. I have a SQL Server 2008 database with a table that includes, among other fields, a geography field that describes road segments. (This data has been imported from TIGER/Line data from the U.S. Census.) I have another fixed point describing a user's location. I want to find...

How do I create a table in SQLite using the VS2008 Server Explorer

Hello, I would like to create a table in a SQLite database using the Server Explorer in VS2008. I am able to do other operations on the database but when it comes to creating a table using the query builder I get Not Supported messages. is there another way other than writing code just for the sole purpose of creating the table? CREATE...

Putting checkbox data into textbox

Is there a way to take multiple check boxes and have the related id/name automatically populate in an un-editable text box/text field? As per one of the suggestions...I am expanding and clarifying. I have several checkboxes that I want saved into the SAME db field...at the same time. So, since I'm a novice programmer where php/sql (...

Mysql query returns only the first letter of strings

Can someone please tell me what I am missing. the following sql query returns only the first letter of strings it fetched from the my database: $result = mysql_query("SELECT * FROM users) or die("error in the query"); thanks. Update $result = mysql_query("SELECT * FROM properties ...

How to combine data from 2 tables under circumstances?

I have 2 tables. One table contains posts and the other contains votes for the posts. Each member can vote (+ or -) for each post. (Structure example:) Posts table: pid, belongs, userp, text. Votes table: vid, userv, postid, vote. Also one table which contains the info for the users. What I want is: Supposing I am a logged-in membe...

SQL Server query freezes when I use "where"

I'm using SQL Server 2005 Express, and I'm running into a strange issue. I have a table called "DailyPrice" that has about 24 million records (I was able to make this table thanks to all your help in this thread: http://stackoverflow.com/questions/1983611/sql-server-2005-slows-down-as-i-keep-adding-rows) Now, I'm running a different fan...

Retrieving a set of all Nth children

The situation: MySQL 5.0 2 tables 1-to-many parent-child foreign key relationship (A.ID = B.PARENT_ID) What's needed: A SELECT query that yields the set of all the Nth children (sorted by their IDs) Also need a form for use in UPDATE Example: Table B | ID | PARENT_ID | ------------------ | 1 | 1 | | 2 | 1 | | ...

Dataset won't commit to database

Hi there... I am a DB programming noob. I need to populate a DB from textbox fields but when I try to then commit it to the DB, I go and view the database and all I see is Nulls... nothing is being saved... please help.. thanks private void btnSubmit_Click(object sender, EventArgs e) { TradesDataSet.TradesRow newTradesRow ...

Retrieve the PK using @@identity

Hi, I'm building a website using ASP.NET and SQL Server, and I use SELECT PK FROM Table WHERE PK = @@identity My question is which is better and more reliable to retrieve the last inserted PK for multiuser website, using @@identity or using this: SELECT MAX(PK) FROM Table WHERE PK = Session ("UserID") ...

little complex sql row postion

basically i have albums, which has 50 images init.. now if i show list of images, i know from which to which row is showing (showing: 20 to 30 of 50), means showing 10 rows from 20 - 30. well now the problem is, i want to select an image, but still show which postion was it selected, so i can move back and forth, but keep the postion too...

is there a way to avoid calling nextval() if the insert fails in PostgreSQL?

In a PostgreSQL database I have a table with a primary key and another field which needs to be unique. CREATE TABLE users ( id INTEGER PRIMARY KEY DEFAULT nextval('groups_id_seq'::regclass), name VARCHAR(255) UNIQUE NOT NULL ); INSERT users (name) VALUES ('foo'); INSERT users (name) VALUES ('foo'); INSERT user...

order by and group by mysql

tell some big, diff between order by and group by, like sort columns data=>order by group it by similar data used for aggregation , order by could be used inside the grouped items , please Tell 5 diff ...

MySQL How to insert new record or update a field depending on whether it exists?

I am trying to implement a rating system where I keep the following two fields in my db table: rating (the current rating) num_rates (the number of ratings submitted so far) UPDATE mytable SET rating=((rating*num_rates)+$theRating)/num_rates, num_rates=num_rates+1 WHERE uniqueCol='$uniqueCol' the variables are from my PHP code. So, b...

C# and PostgreSQL

Can any one show me working example of using cursor returned from PLSQL to c# code? I found many examples showing how to fill dataSet with returned data but I cannot find how to use that cursor with DataReader, as a result I have {unnamed portal}. NpgsqlTransaction tr = (NpgsqlTransaction) Connection.BeginTransaction(); NpgsqlCommand cu...

SQL Server units question

This may be a really dumb question, but... What units does Geography.STLength return? The official MSDN page doesn't say anything about the units returned, and this blog entry here says STLength() returns a float indicating the length of the instance in units. Yes, that's right, it says it returns it in units. Can anyone shed some ligh...

How to rewrite TSQL query for use in Microsoft Access 2003: TSQL Convert

I'd like to rewrite this query for Microsoft Access 2003: SELECT t1.PERSONID ,t1.CARDEVENTDATE ,MIN(t1.CARDEVENTTIME1) AS Intime ,MAX(t2.CARDEVENTTIME1) AS Outtime FROM ( SELECT PERSONID , CARDEVENTDATE , FUNCTIONKEY , CONVERT(VARCHAR(10), SUBSTRING(CARDEVENTTIME...

Overlapping time in SQL server

I am having a table like this ColumnId Intime Outtime 1 01/02/2009 10.00.000 01/02/2009 20.00.0000 2 01/02/2009 2.00.000 01/02/2009 2.00.0000 3 01/02/2009 2.00.000 01/02/2009 5.00.0000 4 01/02/2009 3.3.0.000 01/02/2009 5.00.0000 5 01/02/2009 10.00.000 01/02/2009 22...