SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »

Casimirus: Mar 28, 2005; Yes.

Also, synonyms are pretty worthless for avoiding dynamic SQL, as far as I can tell, you can't do SET IDENTITY_INSERT on a synonym, which means using dynamic SQL anyways.

code:

'asynonym' is not a user table. Cannot perform SET operation.

I guess SET isn't aware of synonyms?

I would love to be wrong though.

# ? Jun 14, 2008 21:44

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 11:16

var1ety: Jul 26, 2004

Stephen posted:

Is there anything I can do to improve the efficiency of this query? The views table is pushing 5 million records, and the photos table is up to near 200 000 so it's really starting to slow down. I have views.auto_id and views.view_type_id both indexed but it's still taking quite awhile.

There aren't any great approaches to solve this problem in a generic way since at the end of the day you still need to aggregate 5 million records, and compute a possibly large outer join. The best thing to do is probably to asynchronously pre-compute part or all of the query and refresh/update on insert or on a timer depending on your needs and your workload. You're probably insert few read many so the additional time added to insert operations is a worthwhile investment.

Make sure you look at MySQL concurrency control and make adequate accommodations so that queries don't block while the tables are refreshing.

# ? Jun 14, 2008 22:10

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

Here's the deal,

I have three tables

"News", "NewsCategories" and "Category"

News has news_Id
NewsCategories has news_Id and category_Id
Category has category_Id

I'm trying to have a recordset where I want news that must have certain categories. I havn't been able to get one working and figured it was pretty basic.

Here's the query I have right now (that obviously doesn't work because there's two AND on the same field):

code:

SELECT `News`.`news_Title` FROM `News`
INNER JOIN `NewsCategories` ON `NewsCategories`.`news_Id` = `News`.`news_Id`
WHERE `category_Id` = 81 AND `category_Id` = 2

# ? Jun 17, 2008 21:24

mezz: Aug 12, 2004; oh polly

code:

SELECT `News`.`news_Title` FROM `News`
INNER JOIN `NewsCategories` ON `NewsCategories`.`news_Id` = `News`.`news_Id`
WHERE (`category_Id` = 81 OR `category_Id` = 2)

alternatively you could use WHERE category_id in(2,81).

# ? Jun 17, 2008 21:28

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

mezz posted:

code:

SELECT `News`.`news_Title` FROM `News`
INNER JOIN `NewsCategories` ON `NewsCategories`.`news_Id` = `News`.`news_Id`
WHERE (`category_Id` = 81 OR `category_Id` = 2)

alternatively you could use WHERE category_id in(2,81).

That works beautifully if it's not strict. But if I want the news to be in category_Id = 81 And category_Id = 2 , what do I do ? :/

Because I don't want news that only match 81 but not 2.

# ? Jun 17, 2008 21:37

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

code:

SELECT `News`.`news_Title` FROM `News`
INNER JOIN `NewsCategories` AS Cat2 ON Cat2.`news_Id` = `News`.`news_Id`
INNER JOIN `NewsCategories` AS Cat81 ON Cat81.`news_Id` = `News`.`news_Id`
WHERE Cat81.`category_Id` = 81 AND Cat2.`category_Id` = 2

I forget the syntax for table aliases in MySQL, but something like this will do what you want.

# ? Jun 17, 2008 21:45

mezz: Aug 12, 2004; oh polly

xenilk posted:

That works beautifully if it's not strict. But if I want the news to be in category_Id = 81 And category_Id = 2 , what do I do ? :/

It depends, will it always be 2 categories? In that case it's not too hard although a bit longer (I didn't test this at all):

code:

SELECT `News`.`news_Title` FROM `News` where news_id in 
(select news_id from newscategories a, newscategories b 
where
 a.news_id=b.news_id and a.category_id=81 and b.category_id=2)

If you want to expand this kind of query on the fly it might cause you some headaches though.

e: well i think both jethro's & mine should work

# ? Jun 17, 2008 21:54

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

Jethro posted:

code:

SELECT `News`.`news_Title` FROM `News`
INNER JOIN `NewsCategories` AS Cat2 ON Cat2.`news_Id` = `News`.`news_Id`
INNER JOIN `NewsCategories` AS Cat81 ON Cat81.`news_Id` = `News`.`news_Id`
WHERE Cat81.`category_Id` = 81 AND Cat2.`category_Id` = 2

I forget the syntax for table aliases in MySQL, but something like this will do what you want.

I think that's it! Here's what I came up with:

code:

SELECT `Gallery`.`gallery_Title` FROM `Gallery`
INNER JOIN `GalleryCategory` AS `CG82` ON `CG82`.`gallery_Id` = `Gallery`.`gallery_Id`
INNER JOIN `GalleryCategory` AS `CG90` ON `CG90`.`gallery_Id` = `Gallery`.`gallery_Id`
WHERE `CG82`.`Category_Id` = 82 AND `CG90`.`Category_Id` = 90

Thanks!

xenilk fucked around with this message at 21:57 on Jun 17, 2008

# ? Jun 17, 2008 21:55

Zombywuf: Mar 29, 2008

Stephen posted:

Is there anything I can do to improve the efficiency of this query? The views table is pushing 5 million records, and the photos table is up to near 200 000 so it's really starting to slow down. I have views.auto_id and views.view_type_id both indexed but it's still taking quite awhile.

Something like this may help

code:

SELECT
	a.*, count(v.id)
FROM
	autos a
	LEFT JOiN views v
		ON a.auto_id = v.auto_id
GROUP BY
	a.*

It may be worth just maintaining a table of viewcounts per auto. If your marshalling DB acces with SPs you can do this in real time easily. Otherwise you'll need to add stuff to your calling code. You can do a similar thing with the photos, storing the most recent photo id associated with the auto. The tables will be much smaller and ought to be able to make better use of indexes.

# ? Jun 17, 2008 23:04

chocojosh: Jun 9, 2007; D00D.

How would you go about importing a CSV file into the database with some custom logic and be able to report errors (ideally by sending an e-mail)? We're using SQL Server 2005.

Essentially each row in the CSV file should have an ID that matches a record in a database table. If there is a match, then there is an insert done in a separate table using the values from the CSV file. If there is no match, then we may need to report an error with the information from the CSV file. If there are rows in the DB table that are not in the CSV file, then we may need to do an update or report an error depending on the values in the table.

My supervisor suggested taking a look into Sql Server Integration Services, which I know absolutely nothing about. Any recommendations for where to learn about SSIS or if it can do what I need it to do? Any suggestions on other tools to look into.

This would be running from a windows service that would execute the script once a day automatically.

# ? Jun 17, 2008 23:15

Ardhanari: Mar 23, 2006

chocojosh posted:

How would you go about importing a CSV file into the database with some custom logic and be able to report errors (ideally by sending an e-mail)? We're using SQL Server 2005.

Look into BULK INSERT.

# ? Jun 18, 2008 00:02

Fangs404: Dec 20, 2004; I time bomb.

I've Googled around, but I can't find anything, so I figured I'd ask here. I'm working with HSQLDB right now, and it's got some interesting (read: frustrating) things going on with its quotation requirements. Now, here're the tables that matter for this problem:

code:

CREATE TABLE "nodes" (
	id IDENTITY,
	name VARCHAR NOT NULL
)

CREATE TABLE "edges" (
	id IDENTITY,
	startNodeID INT NOT NULL,
	endNodeID INT NOT NULL,
	edgeType TINYINT NOT NULL,
	FOREIGN KEY (startNodeID) REFERENCES "nodes"(id),
	FOREIGN KEY (endNodeID) REFERENCES "nodes"(id)
)

CREATE TABLE "temporalEdges" (
	id IDENTITY,
	edgeID INT NOT NULL,
	time TIMESTAMP NOT NULL,
	FOREIGN KEY (edgeID) REFERENCES "edges"(id)
)

Normally, something like this would work with the average database:

code:

SELECT startNodeID, endNodeID FROM edges WHERE edgeType = 1

However, with HSQLDB, this change is needed:

code:

SELECT startNodeID, endNodeID FROM "edges" WHERE edgeType = 1

That's all fine and dandy, and it only took me about 10 mins to figure that out, but now I'm trying to do something a little more complicated, and I'm having a hard time figuring out what I'm doing wrong. Here's what I'm trying to do:

code:

SELECT edgeID, time FROM "temporalEdges" WHERE edgeID = "edges"."id" AND "edges"."edgeType" = 1

It's just not working. This is the error I get:

code:

java.sql.SQLException: Column not found: edges.id in statement [SELECT edgeID, time FROM "temporalEdges" WHERE edgeID = "edges"."id" AND "edges"."edgeType" = 1]

Does anyone know what I'm doing wrong? I'm sure it's stupidly easy to fix, but I've tried every permutation of quotes, and nothing seems to be working. Thanks!

Fangs404 fucked around with this message at 16:30 on Jun 18, 2008

# ? Jun 18, 2008 00:40

mezz: Aug 12, 2004; oh polly

Well you never included the table edges in your FROM statement

code:

SELECT edgeID, time FROM "temporalEdges","edges"
WHERE edgeID = "edges"."id" AND "edges"."edgeType" = 1

# ? Jun 18, 2008 03:05

Fangs404: Dec 20, 2004; I time bomb.

mezz posted:

Well you never included the table edges in your FROM statement
code:
SELECT edgeID, time FROM "temporalEdges","edges"
WHERE edgeID = "edges"."id" AND "edges"."edgeType" = 1

Holy poo poo. :love:

# ? Jun 18, 2008 03:29

Fangs404: Dec 20, 2004; I time bomb.

Well, poo poo, I thought that was the problem, but apparently it's not. I got into work today, and it's still giving me the same error. Here's the error now:

code:

java.sql.SQLException: Column not found: edges.id in statement [SELECT edgeID, time FROM "temporalEdges", "edges" WHERE edgeID = "edges"."id" AND "edges"."edgeType" = 1]

# ? Jun 18, 2008 16:24

chocojosh: Jun 9, 2007; D00D.

Ardhanari posted:

Look into BULK INSERT.

Bulk insert doesn't seem to support custom rules.

I'm guessing the best thing to do would be to first check the database and ensure that our input is valid. If there are any errors, report them first, and keep the valid data and use bulk insert on the valid data?

# ? Jun 18, 2008 16:43

mezz: Aug 12, 2004; oh polly

Fangs404 posted:

Well, poo poo, I thought that was the problem, but apparently it's not. I got into work today, and it's still giving me the same error. Here's the error now:

I suspect it will have something to do with this?

quote:

Tables and columns which are created with
SchemaUpdate/SchemaExport are always in uppercase.

The fact that you created the table edges with your fieldnames not in between quotes will have resulted in a table "edges" with fields ID,STARTNODEID etc.
So I think edges."ID" and edges."EDGETYPE" (and probably edges.id and edges.edgetype) might work.

I had a bit a similar problem when I started with PostgreSQL actually, there everything gets converted to lowercase.

# ? Jun 18, 2008 17:28

Fangs404: Dec 20, 2004; I time bomb.

mezz posted:

I suspect it will have something to do with this?

The fact that you created the table edges with your fieldnames not in between quotes will have resulted in a table "edges" with fields ID,STARTNODEID etc.
So I think edges."ID" and edges."EDGETYPE" (and probably edges.id and edges.edgetype) might work.

I had a bit a similar problem when I started with PostgreSQL actually, there everything gets converted to lowercase.

poo poo, you're right. That worked. I just added quotes around every column name in both the CREATE statements and in the SELECT and INSERT statements. What a pain in the rear end, but at least it works. Thanks!

# ? Jun 18, 2008 17:35

Ardhanari: Mar 23, 2006

chocojosh posted:

Bulk insert doesn't seem to support custom rules.

I'm guessing the best thing to do would be to first check the database and ensure that our input is valid. If there are any errors, report them first, and keep the valid data and use bulk insert on the valid data?

I've never used the FIRE_TRIGGERS part before, but I think you could do it through that -- just set up a trigger on the destination table with whatever custom logic you need (checking that every row in Inserted has a corresponding ID, etc). You should even be able to send emails from the trigger, but make sure you only do it under strictly specified conditions unless you want whoever's running your email server to cut your throat.

# ? Jun 18, 2008 21:22

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

How would you go about importing a CSV file into the database with some custom logic and be able to report errors (ideally by sending an e-mail)? We're using SQL Server 2005.

Essentially each row in the CSV file should have an ID that matches a record in a database table. If there is a match, then there is an insert done in a separate table using the values from the CSV file. If there is no match, then we may need to report an error with the information from the CSV file. If there are rows in the DB table that are not in the CSV file, then we may need to do an update or report an error depending on the values in the table.

My supervisor suggested taking a look into Sql Server Integration Services, which I know absolutely nothing about. Any recommendations for where to learn about SSIS or if it can do what I need it to do? Any suggestions on other tools to look into.

This would be running from a windows service that would execute the script once a day automatically.

SSIS absolutely can do what you need it to. Here's the Books On Line page. I don't know how useful this page is, since when I started learning I had some existing packages to look at. But I guarantee that if you need to do more than just straight loading of data, SSIS is probably the way to go.

# ? Jun 18, 2008 21:46

Zombywuf: Mar 29, 2008

SSIS is painful, please don't use it, you'll only encourage MS to make it 'better'. Also, using triggers to enforce constraints is bad, just don't do it.

Your best option is to either use bcp.exe (if you need to trigger the the load externally) or bulk insert (if you want to trigger it from the db) to load the data into a separate table. Then you do something like:

code:

INSERT INTO
  actual_table
SELECT
  *
FROM
  loading_table
WHERE
  <data is correct>

IF EXISTS (SELECT * FROM loading_table WHERE not <data is correct>) sp_senddbemail ...

ps. bcp can be a bit of a pain, this makes it less so

# ? Jun 19, 2008 00:28

Acer Pilot: Feb 17, 2007; put the 'the' in therapist

code:

table: lyrics
id, title, artist, album, lyrics

table: albums
id, title, year

query:
SELECT l.album, l.title, a.year
FROM `lyrics` AS l, albums AS a
WHERE l.album = a.id
ORDER BY a.year DESC

I have two tables setup like the above and my query grabs all the lyrics and organizes them by an album's year. What I'm trying to do is paginate the results but I don't know how to do this while keeping an album intact (not split into two pages).

Any ideas?

Acer Pilot fucked around with this message at 06:52 on Jun 19, 2008

# ? Jun 19, 2008 06:35

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

From a previous code I made a query (that varies alot and as you can see it can create a lot of inner join.

GALLERY_ID and CATEGORY_ID are indexed aswell

code:

SELECT   DISTINCT (`GALLERY`.`GALLERY_ID`)
FROM     `GALLERY`
         INNER JOIN `GALLERYCATEGORY` AS `A`
           ON `A`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT38`
           ON `CAT38`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT52`
           ON `CAT52`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT53`
           ON `CAT53`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT74`
           ON `CAT74`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT96`
           ON `CAT96`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
WHERE    (`A`.`CATEGORY_ID` = 82)
         AND (`CAT38`.`CATEGORY_ID` != 38
              AND `CAT52`.`CATEGORY_ID` != 52
              AND `CAT53`.`CATEGORY_ID` != 53
              AND `CAT74`.`CATEGORY_ID` != 74
              AND `CAT96`.`CATEGORY_ID` != 96)

ORDER BY RAND()
LIMIT    1;

I'm thinking that the Inner Join makes it very painful for the SQL server since it has to query the table over and over again for the same field. Is there any more efficient way to do this?

Thanks!

# ? Jun 19, 2008 17:26

chocojosh: Jun 9, 2007; D00D.

xenilk posted:

From a previous code I made a query (that varies alot and as you can see it can create a lot of inner join.

GALLERY_ID and CATEGORY_ID are indexed aswell

code:

SELECT   DISTINCT (`GALLERY`.`GALLERY_ID`)
FROM     `GALLERY`
         INNER JOIN `GALLERYCATEGORY` AS `A`
           ON `A`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT38`
           ON `CAT38`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT52`
           ON `CAT52`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT53`
           ON `CAT53`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT74`
           ON `CAT74`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
         INNER JOIN `GALLERYCATEGORY` AS `CAT96`
           ON `CAT96`.`GALLERY_ID` = `GALLERY`.`GALLERY_ID`
WHERE    (`A`.`CATEGORY_ID` = 82)
         AND (`CAT38`.`CATEGORY_ID` != 38
              AND `CAT52`.`CATEGORY_ID` != 52
              AND `CAT53`.`CATEGORY_ID` != 53
              AND `CAT74`.`CATEGORY_ID` != 74
              AND `CAT96`.`CATEGORY_ID` != 96)

ORDER BY RAND()
LIMIT    1;

I'm thinking that the Inner Join makes it very painful for the SQL server since it has to query the table over and over again for the same field. Is there any more efficient way to do this?

Thanks!

Why are you joining the same table 6 times?

Couldn't this work:

code:

SELECT (DISTINCT G.Gallery_ID)
FROM Gallery G
INNER JOIN GalleryCategory GC ON G.Gallery_ID = GC.Gallery_ID
WHERE GC.Category_ID NOT IN (38,52,53,74,82,96)

# ? Jun 19, 2008 20:13

mezz: Aug 12, 2004; oh polly

I think he wants all the galleries who belong to category 82 but not to 38,52,53,74,82,96.
So if I got it right, if a gallery has categories '82' and '1' it should fit, if it's in 82,1,38 it should be left out?

I think this won't be too far off:

code:

SELECT gallery.* FROM gallery WHERE gallery_id in 
(
   SELECT gallery_id from gallerycategory WHERE category_id in(82) AND
   gallery_id NOT IN
   ( 
     SELECT gallery_id from gallerycategory where category_id in (38,52,53,74,82,96) 
     GROUP BY gallery_id
   )
   GROUP BY gallery_id
   HAVING count(*)>=1
);

Like this it's pretty easy to change, say you want everything in cat. 82 and 1 but not in 3,2,7
it would become

code:

SELECT gallery.* FROM gallery WHERE gallery_id in 
(
   SELECT gallery_id from gallerycategory WHERE category_id in(82,1) AND
   gallery_id NOT IN
   ( 
     SELECT gallery_id from gallerycategory where category_id in (3,2,7) 
     GROUP BY gallery_id
   )
   GROUP BY gallery_id
   HAVING count(*)>=2
);

# ? Jun 19, 2008 20:50

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

mezz, you are exactly right!

I will try the way you wrote, could you explain to me that specific part

code:

GROUP BY gallery_id 
HAVING count(*)>=2

I figure the 2 is the number of numbers I have in the first SELECT IN but I'm not sure I get what it means. Thanks!

# ? Jun 19, 2008 21:02

mezz: Aug 12, 2004; oh polly

Actually it should just be count(*)=x.

I made a quick test, gallery_id=o_id,cat_id=p_id and the table is named 'a' :v:

Say you have these elements :

code:

o_id|p_id
1 | 1
1 | 2
1 | 7
1 | 4
2 | 1
2 | 4
2 | 3
3 | 2
3 | 4

and you want the records that have both 4 & 1 in it but not 7.
Let's take a look at this count thing:

code:

SELECT count(*),o_id
  FROM a
where p_id in (4,1) and
o_id not in (select o_id from a where p_id in(7) group by o_id)
group by o_id

will give this:

code:

count|o_id
1 | 3
2 | 2

We count only 1 row for Gallery_id 3 so it either has only a row with 4 in it or 1 , but not both; it should be left out.
This won't work if you have duplicates in the table, the combination o_id & p_id has to be unique.

# ? Jun 19, 2008 22:58

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

mezz posted:

Awesome explanation

Thanks for explaining, it completly makes sense now

Although, I'm still running into "errors"

code:

SELECT Gallery.gallery_Id
FROM Gallery 
WHERE Gallery_Id in 
( 
	SELECT Gallery_Id from GalleryCategory
	WHERE Category_Id in(5,39) AND Gallery_Id NOT IN 
	( 
		SELECT Gallery_Id 
		from GalleryCategory
		where Category_Id in (10,37,38,52,62,65,74,96) 
		GROUP BY Gallery_Id 
	) 
	GROUP BY Gallery_Id 
	HAVING count(*)>=2 
);

Takes more than 1 minute to execute, is there anyway to make it faster?

# ? Jun 20, 2008 16:07

chocojosh: Jun 9, 2007; D00D.

Mezz: Thanks for the clarification, I misread the = for != in the first WHERE statement (and this was a great example why when you ask for help you should also explain *what* you want to do instead of your solution

)

Regarding SSIS: my supervisor insists that I use SSIS for this little tool I'm working on. I think they're hinting that I test out a new technology for the company (I've shown interest before in learning new stuff).

Any books you'd recommend? I've been given permission from the CTO to go pick up a book and have it expensed by the company.

Jethro: Thanks for the BOL page. I'd rather have a real book to start off with (I tend to prefer real books over online books for learning stuff. I prefer using online books/articles when I'm already comfortable with the tool/language and need to do a very specific task).

# ? Jun 20, 2008 16:12

No Safe Word: Feb 26, 2005

chocojosh posted:

Regarding SSIS: my supervisor insists that I use SSIS for this little tool I'm working on. I think they're hinting that I test out a new technology for the company (I've shown interest before in learning new stuff).

Any books you'd recommend? I've been given permission from the CTO to go pick up a book and have it expensed by the company.

I've been working with SSIS for about a year now and the two Wrox books I used to get started: Professional SQL Server 2005 Integration Services and the Expert counterpart were both pretty good.

Also, reading through Jamie Thomson's blog entries are good too: http://blogs.conchango.com/jamiethomson/

# ? Jun 20, 2008 16:16

lhunc: Jan 4, 2007

edit: ignore me

lhunc fucked around with this message at 17:07 on Jun 20, 2008

# ? Jun 20, 2008 16:52

Ardhanari: Mar 23, 2006

xenilk posted:

Thanks for explaining, it completly makes sense now

Although, I'm still running into "errors"

Takes more than 1 minute to execute, is there anyway to make it faster?

Use EXCEPT to get rid of that innermost block comparison.

code:

SELECT Gallery.gallery_Id
FROM Gallery 
WHERE Gallery_Id in 
( 
	SELECT Gallery_Id from GalleryCategory
	WHERE Category_Id in(5,39)
	EXCEPT
	SELECT Gallery_Id FROM GalleryCategory
	WHERE Category_Id IN (10, 37, 38, 52, 62, 65, 74, 96)
);

I'm stuck with 2000 here so I can't test it (EXCEPT is a completely different animal) but something along those lines should work. I'm not sure where the HAVING and GROUP BY clauses would go.

# ? Jun 20, 2008 17:52

mezz: Aug 12, 2004; oh polly

xenilk posted:

Takes more than 1 minute to execute, is there anyway to make it faster?

I'm afraid it's as good as it gets, I don't see any way to reduce it even more but I could be wrong. In any case it runs in <5 seconds on 300 000 rows with nothing cached and without indexes on my crappy laptop so it's not supposed to be that slow.

So I'm guessing you're using Mysql on some shared hosting? You get what you pay for

# ? Jun 20, 2008 17:58

xenilk: Apr 17, 2004; ERRYDAY I BE SPLIT-TONING! Honestly, its the only skill I got other than shooting the back of women and calling it "Editorial".

mezz posted:

I'm afraid it's as good as it gets, I don't see any way to reduce it even more but I could be wrong. In any case it runs in <5 seconds on 300 000 rows with nothing cached and without indexes on my crappy laptop so it's not supposed to be that slow.

So I'm guessing you're using Mysql on some shared hosting? You get what you pay for

That is bizarre, I am using a dedicated server at iweb8.com with these specs:

Processor #1 Vendor: GenuineIntel
Processor #1 Name: Intel(R) Pentium(R) D CPU 2.80GHz
Processor #1 speed: 2800.391 MHz
Processor #1 cache size: 2048 KB

Processor #2 Vendor: GenuineIntel
Processor #2 Name: Intel(R) Pentium(R) D CPU 2.80GHz
Processor #2 speed: 2800.391 MHz
Processor #2 cache size: 2048 KB

and 1024meg of ram

My gallery table has 44121 rows, gallerycategory has 1174 rows and gallery has 97 rows.

# ? Jun 20, 2008 18:36

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

No Safe Word posted:

chocojosh posted:
Regarding SSIS: my supervisor insists that I use SSIS for this little tool I'm working on. I think they're hinting that I test out a new technology for the company (I've shown interest before in learning new stuff).

Any books you'd recommend? I've been given permission from the CTO to go pick up a book and have it expensed by the company.

I've been working with SSIS for about a year now and the two Wrox books I used to get started: Professional SQL Server 2005 Integration Services and the Expert counterpart were both pretty good.

Also, reading through Jamie Thomson's blog entries are good too: http://blogs.conchango.com/jamiethomson/

I can too can vouch for both the blog and the first book (I have not read the Expert book).

# ? Jun 20, 2008 22:47

Factor Mystic: Mar 20, 2006; Baby's First Post-Apocalyptic Fiction

I've got some questions for a new hobby project. It's a web bookmark database type thing, and there will be user-added keywords associated with each url. I want to be able to search by keyword, and I want to do this in an intelligent way. I know not to just dump all the keywords into a big Text field, but I don't know the proper way to handle this. Can anyone post some tips or basic-intermediate articles on this type of thing?

# ? Jun 21, 2008 23:35

Ardhanari: Mar 23, 2006

Factor Mystic posted:

I've got some questions for a new hobby project. It's a web bookmark database type thing, and there will be user-added keywords associated with each url. I want to be able to search by keyword, and I want to do this in an intelligent way. I know not to just dump all the keywords into a big Text field, but I don't know the proper way to handle this. Can anyone post some tips or basic-intermediate articles on this type of thing?

Each URL can have many keywords, and keywords can have multiple bookmarks 'tagged', right? Set up a Keywords(KeywordID, KeywordData, ...) table and a Bookmark_Keyword (BookmarkID, KeywordID) to create your many-to-many relation. Then you can get the keywords for a URL by querying through the bridge table, or get all bookmarks with a particular tag the same way.

# ? Jun 22, 2008 16:58

Factor Mystic: Mar 20, 2006; Baby's First Post-Apocalyptic Fiction

Ardhanari posted:

Each URL can have many keywords, and keywords can have multiple bookmarks 'tagged', right? Set up a Keywords(KeywordID, KeywordData, ...) table and a Bookmark_Keyword (BookmarkID, KeywordID) to create your many-to-many relation. Then you can get the keywords for a URL by querying through the bridge table, or get all bookmarks with a particular tag the same way.

Alright, in that setup I don't understand how a bookmark is related to more than one keyword. If each KeywordID links to only one keyword (KeywordData), how can more than one keyword be saved for a bookmark? I guess I don't understand the proper way to store a variable length list of data.

# ? Jun 22, 2008 19:51

Ardhanari: Mar 23, 2006

Factor Mystic posted:

Alright, in that setup I don't understand how a bookmark is related to more than one keyword. If each KeywordID links to only one keyword (KeywordData), how can more than one keyword be saved for a bookmark? I guess I don't understand the proper way to store a variable length list of data.

Keyword.KeywordID links to Bookmark_Keyword.KeywordID. Bookmark_Keyword.BookmarkID then links to Bookmark.BookmarkID. You'll have unique keywords (if you want) but they link to multiple Bookmark_Keyword entries and thus to multiple bookmarks (and vice versa).

code:

Keyword		Bookmark_Keyword	Bookmark
ID Name		K_ID	B_ID		ID Name
1  cool		1	3		1  google
2  shop		2	2		2  amazon
3  search	3	1		3  yahoo
		3	3

So Google is both 'cool' and 'search', and the 'search' keyword covers both Google and Yahoo. You're not storing lists, you're storing relations. If you want to store your tags as flat fields, you might as well replace SQL with Notepad. The relational database lets you say 'okay, item type one hooks up to item type two like this' -- with other side benefits like avoiding duplication (imagine how many things would get tagged with 'cool' or 'poo poo' in a flat list structure!) and faster querying.

ninja edit: holy poo poo, I didn't realize I had an encoded message about markup in there. Unintended, but I'm leaving that in!

# ? Jun 22, 2008 21:44

Adbot: ADBOT LOVES YOU

# ? May 11, 2024 11:16

Factor Mystic: Mar 20, 2006; Baby's First Post-Apocalyptic Fiction

Ardhanari posted:

Keyword.KeywordID links to Bookmark_Keyword.KeywordID. Bookmark_Keyword.BookmarkID then links to Bookmark.BookmarkID. You'll have unique keywords (if you want) but they link to multiple Bookmark_Keyword entries and thus to multiple bookmarks (and vice versa).
code:
Keyword		Bookmark_Keyword	Bookmark
ID Name		K_ID	B_ID		ID Name
1  cool		1	3		1  google
2  shop		2	2		2  amazon
3  search	3	1		3  yahoo
		3	3
So Google is both 'cool' and 'search', and the 'search' keyword covers both Google and Yahoo. You're not storing lists, you're storing relations. If you want to store your tags as flat fields, you might as well replace SQL with Notepad. The relational database lets you say 'okay, item type one hooks up to item type two like this' -- with other side benefits like avoiding duplication (imagine how many things would get tagged with 'cool' or 'poo poo' in a flat list structure!) and faster querying.

ninja edit: holy poo poo, I didn't realize I had an encoded message about markup in there. Unintended, but I'm leaving that in!

Alright, this is making a lot more sense now with the example tables there. I got that I would need a Keyword and a Bookmark table, but I didn't know how to properly link to the two together. So each row of the Bookmark_Keyword table contains exactly one Keyword <-> Bookmark relation. Now I get it. Thank you

# ? Jun 22, 2008 22:46

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

«‹›303 »