Your browser does not seem to support JavaScript. As a result, your viewing experience will be diminished, and you may not be able to execute some actions.

Please download a browser that supports JavaScript, or enable it if it's disabled (i.e. NoScript).

Should we create new tables instead of filtering 1 large table?

S
simrah last edited by

In an application with groups and posts where each post can be in exactly 1 group, we can have the table POSTS(post_id, group_id, post_content). However, there's gonna be a lot of posts in a lot of groups and filtering for posts in 1 or more groups can take a lot of resources. Instead, I think we can just create a new table $(GROUP_ID)_POSTS(post_id, post_content) where $GROUP_ID is the group_id from the POSTS table. I don't know about the specifics but I think joining several tables is gonna be a lot faster than filtering through a lot of group_ids.

Is this a good way to improve performance? Why or why not?
Reply Quote 0
1 Reply Last reply
D
Duquan last edited by

The "Table-per-whatever" Model almost always breaks down eventually.
It's usually when some "Administrative" function needs to go across the 20,000 tables you've built up in one query, but the DBMS limits you to just 61 tables in any one query (MySQL).

Any half-decent DBMS with sensibly-indexed Tables will cope with your first structure (post_id, group_id, post_content) with ease.
Reply Quote 0
1 Reply Last reply

2
Posts

0
Views

Suggested Topics

Join 4 tables and combine 1 table multiple row into one based on a key value
SQL, Database Testing • mysql join • • Analeea

2

0
Votes

2
Posts

0
Views

A

select m.meetings_id,client_name,group_concat(concat(cc.contact_name)),summary from meetings m left Join client c on c.client_id = m.client_id left join client_contact cc on cc.client_id= c.client_id left join meeting_attendees ma on cc.contact_id = ma.contact_id group by m.meetings_id order by m.meetings_id
K

Estimated plan generation succeeds for batch creating and using #temp table but fails with a permanent table
SQL, Database Testing • sql server exec languageution plan • • kalena

2

0
Votes

2
Posts

0
Views

Z

In SQL Server 2000 the #temp table version does fail with the message Invalid object name '#temp'. However since SQL Server 2005 compiling a plan with a local temporary table does actually create the temporary table behind the scenes. You can see this by getting the estimated plan for Create table #temp ( ID INT IDENTITY (1,1) ) Insert into #temp DEFAULT VALUES SELECT * FROM #temp OPTION (RECOMPILE, QUERYTRACEON 8605, QUERYTRACEON 3604); DROP TABLE #temp Which produces an output like The negative object id is the id of the object fleetingly created. Polling tempdb.sys.tables in a loop whilst generating the estimated plan and comparing with the TableID subsequently output can show this. The estimated plan you see for the statements referencing the temp table is of limited use however as even the addition of a single row to the temp table will trigger a statement level recompile and potentially a different plan when you actually execute the SQL. This behaviour does not apply to global temporary tables or permanent tables. I presume this is also the same reason as /*Works fine (assuming T doesn't exist)*/ IF 1 = 1 SELECT 1 AS X INTO T ELSE SELECT 1 AS X INTO T /*Fails (There is already an object named '#T' in the database.)*/ IF 1 = 1 SELECT 1 AS X INTO #T ELSE SELECT 1 AS X INTO #T
J

Speeding up SELECT FROM a large table
SQL, Database Testing • mysql performance select innodb mariadb 10.5 • • Jolied

2

0
Votes

2
Posts

1
Views

Z

If you select 1M rows, your query can't be fast. If you write the result on disk, the time is probably more than doubled. But you say that writing to a different disk doesn't make the query any faster. That could be because the table reading and the file writing are not parallelised, I don't know. But it could also indicate that the bottleneck is the CPU decompressing the columns. Unfortunately you can't decide which compression algorithm will be used, it's always zlib. Maybe you can try InnoDB page compression instead. You'll be able to try different algorithms (innodb_compression_algorithm) and compression levels (innodb_compression_level). Also if the pages you're reading are in the buffer pool, they're stored uncompressed, so less uncompressing work is needed.
Not able to create this table
SQL, Database Testing • mysql mysql 5.7 ddl • • Demir

2

0
Votes

2
Posts

0
Views

K

Recommendations Use a table name without spaces. Remove the apostrophes from 'ord_id' and 'part_id'. Solution CREATE TABLE OrderParts ( ord_id INT(7) NOT NULL AUTO_INCREMENT PRIMARY KEY , part_id INT(7) NOT NULL , inv_id INT(7) NOT NULL , ord_shipdate DATE NOT NULL , ord_received DATE NOT NULL , ord_status VARCHAR(15) NOT NULL , ord_cost DECIMAL NOT NULL );
C

How to most efficiently find out if a record has child records?
SQL, Database Testing • postgresql performance performanc languagee tuni • • charlenef

2

0
Votes

2
Posts

0
Views

B

Don't forget that Postgres has a boolean datatype. The following is the most succinct way to express the query: select parent_id, name, exists (select from child where parent_id = p.parent_id) as has_children from parent p; https://dbfiddle.uk/?rdbms=postgres_10&fiddle=86748ba18ba8c0f31f1b77a74230f67b
J

Concatenate 2-3 table field values in 1 table field in access
SQL, Database Testing • ms access c languageonc languageat • • Jolied

2

0
Votes

2
Posts

0
Views

J

You shouldn't need to concatenate the fields at all. You can find out whether a customer has filled out the survey for a given month and year by using all 3 columns: SELECT * FROM Surveys WHERE CustomerID = 1 AND SurveyMonth = Month(Date()) AND SurveyYear = Year(Date()) The Date() function returns the current system date. The Month() and Year() functions return the month and year, respectively, of the date passed to them. A suggestion: store the entire date a survey was taken as a Date type in the database rather than using separate columns for month and year. This will simplify lookups and storage, making the query above look more like: SELECT * FROM Surveys WHERE CustomerID = 1 AND Month(SurveyDate) = Month(Date()) AND Year(SurveyDate) = Year(Date()) where SurveyDate is a column of Date type.
T

Maximum number of TEXT/BLOB columns per table/performance of table with many of them?
SQL, Database Testing • mysql performance database design mysql 5.6 sc languagehema • • Trenton

2

0
Votes

2
Posts

0
Views

K

If you lump all the column_## columns into a JSON field, you can have a limit of 4GB. The question of how much gets complicated. InnoDB has 4 ROW_FORMATs: REDUNDANT, COMPACT, DYNAMIC, COMPRESSED. I think you could have 400 columns of VARCHAR(255) or TEXT (or ...) in ROW_FORMAT=DYNAMIC. That format uses a 20-byte pointer in the record to where the actual text is stored. If you changed the block size from the default 16KB to 32KB, you could get about 800 columns. Going to 64KB would hit the limit of 1017 columns (in 5.6.9). Changing the block size is non trivial, so I don't recommend it. More Limits. (Hmmm... maybe I should add your question there.) These two statements do go together: "performance of this would be horrendous" and "I do need to filter against the content of the columns". This is because DYNAMIC has an extra disk fetch for each column you need to 'filter' on. On the other hand, in some situations (short text, not too many columns, etc), the entire text of some of the columns may be stored inline. COMPACT might actually be better. This format tries to keep the text in the record. But if the record would become bigger than about 8K, it peels off the longer cells for off-block storage. It will put the first 768 bytes of record inline. From that, you are limited to about 10 columns. (Try 12 columns, it will spit at you.) I believe you will need innodb_file_per_table=1 for DYNAMIC. How will you be 'filtering'?
why should I join 2 tables with 1 table apart
SQL, Database Testing • mysql join • • Bogopo

2

0
Votes

2
Posts

0
Views

T

It's a bit of a guess because you haven't provided the link to the challenge (in markdown you have to make links like [this problem](http://whatever.com) but clicking your link just takes to http://this%20problem, so I guess the markdown is broken You've said you have tables: Hacker - I guess this is like a user table Challenge - I guess this is a problem to solve Submission - I guess it's an answer a hacker has made to a problem So we can clearly have "one hacker may answer many challenges" and "one challenge has many hackers answering it" ie a many many relationship so we need a table in the middle to split it down and track which hacker answered which challenge, and this is the Submissions table doing this breakdown This interesting part here, and possibly put in deliberately to trip people up, is that Challenge also has a hacker Id column. For some reason hacker rank's tables track a hacker-challenge relationship that is nothing to do with a submission ie the hacker ID in the challenge table is something where there can be only one hacker for that challenge, such as the hacker that created it, or the hacker that first got perfect score on it, or the hacker who is responsible for marking the answers on it, or the hacker that most recently took it.. Whatever the reason, you said you joined on that hacker ID which in my mind is the wrong thing to do because it bears no relation to the question of multiple hackers who have submitted: it could be that the hacker in the challenge.hacker_id has never even made a submission on the challenge, they simply wrote it and have never answered it. It therefore cannot give you the answers the challenge seeks. It's probably meant to catch you out and it's one reason why when I'm designing a database schema I aim to put the reason for a column into its name in cases like this; ie in the Challenge table I might have called it author_hackerid or moderator_hackerid to describe not only where it links to (hacker table on hacker ID) but why it links there (author, moderator, firstwinner, recenttaker) especially if there are multiple people.. (though in such a case it may make more sense to have another table, like submission) that splits hacker and challenge, and gives a reason for why that hacker is related to that challenge) Remember, that just because two columns are named the same it doesn't mean they are alternative join options. Even columns that have participated in a join before are not alternative, if it's a left join. If it's an inner join, fair game: a INNER JOIN b ON a.id = b.id INNER JOIN c ON b.id = c.id a INNER JOIN b ON a.id = b.id INNER JOIN c ON a.id = c.id ^ but these can give different results: a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON b.id = c.id a LEFT JOIN b ON a.id = b.id LEFT JOIN c ON a.id = c.id That small but critical difference in the second query of both tables left joining to a might be huge
J

Should I only have 1 table "User" Instead of 2 "Client" and "Worker"
SQL, Database Testing • mysql database design • • juvenalb

2

0
Votes

2
Posts

1
Views

K

ClientID as separate table isn't necessary, as long one user has only one clientId, what you implied by your 1:1 relatiomnship, as it only is an attribute to user table. But as you want to plan ahead, i would keep it as bridge table, a user can have more than one clientId. like the user_type table
Y

Postgres Order By but only the most recent of a particular type
SQL, Database Testing • postgresql performance query performance group by order by • • yajahira

2

0
Votes

2
Posts

0
Views

B

Solution 1 CREATE TABLE pet ( name text, type text, date date); INSERT INTO pet VALUES ('Nemo', 'Fish', 'June 1, 2016'), ('Fido', 'Dog', 'January 1, 2016'), ('Felix', 'Dog', 'February 1, 2016'), ('Whiskers', 'Cat', 'April 1, 2016'), ('Marlin', 'Fish', 'August 1, 2016'), ('Shifu', 'Cat', 'March 3, 2016'); Using PostgreSQL-specific DISTINCT ON clause we can do this in a single table scan: SELECT name, type, date FROM ( SELECT DISTINCT ON ( type, CASE WHEN type<>'Dog' THEN date END ) name, type, date FROM pet ORDER BY type, CASE WHEN type<>'Dog' THEN date END, date DESC ) AS subq1 ORDER BY date; Solution 2 Using more ANSI-compatible SQL, like UNION and LIMIT. It will work on MySQL, DB2 and some others. Similar solution can be done on Oracle, just replace LIMIT with ROWNUM. ( SELECT * FROM pet WHERE type = 'Dog' ORDER BY date DESC LIMIT 1 ) UNION ALL ( SELECT * FROM pet WHERE type <> 'Dog' ) ORDER BY date; Notes on performance With small tables (less than 1000000 rows), any solution will work. DISTINCT ON is slightly faster: Iterations: 100000 Query: /*q1*/ SELECT name, type, date FROM ( SELECT DISTINCT ON ( type, CASE WHEN type<>'Dog' THEN date END ) name, type, date FROM pet ORDER BY type, CASE WHEN type<>'Dog' THEN date END, date DESC ) AS subq1 ORDER BY date; Time: 24.834 s (31%) Average: 0.248 ms Rows: 500000 Winner: 80914 times (80%) Query: /*q2*/ ( SELECT * FROM pet WHERE type = 'Dog' ORDER BY date DESC LIMIT 1 ) UNION ALL ( SELECT * FROM pet WHERE type <> 'Dog' ) ORDER BY date; Time: 26.778 s (33%) Average: 0.268 ms Rows: 500000 Winner: 12881 times (12%) Query: /*q3*/ select name, type, date from ( select pet.*, case when type = 'Dog' then rank() over (partition by type order by date desc) else 1 end as rnk from pet ) as x where rnk = 1 order by date; Time: 27.490 s (34%) Average: 0.275 ms Rows: 500000 Winner: 6205 times (6%) With "big data", make sure your query does only one, indexed, table scan.
J

JMeter: Getting error in non GUI mode
Automated Testing • java performance jmeter load testing • • jules

2

0
Votes

2
Posts

1
Views

C

Answering the comments above, Well I had Java and Jmeter installed correctly with ClassPath and everything. I also tried running Command Prompt as Administrator but got the same result. There was no problem in that. That is why the GUI version was working properly in first place. After a lot of R&D and Googling I found out that the problem was with the findstr command in the jmeter.bat file. To fix this you need to do the following: Go to Properties of the "My Computer" or "This PC" on the desktop (Right click and select Properties). In Windows 8 select Advances System Properties in the window that appears. Select Environment Variables. Set Path variable to "C:\WINDOWS\system32". If Path variable is already set for another path then add a semi-colon(;) at the end and then write the above path and save it. Now restart your command prompt and run your JMeter test.
K

Which is faster, SUM(CASE) or CTE PIVOT?
SQL, Database Testing • sql server performance query performance pivot • • kalena

2

0
Votes

2
Posts

0
Views

F

Are there examples where PIVOT is slower? This is unlikely in simple cases. As Itzik Ben-Gan notes in his SQL Server Pro article, Pivoting Data when looking at the plan for a PIVOT query (emphasis added): Figure 3 shows the plan for the PIVOT query. As you can see, this plan is very similar to that of the standard solution—so much so that if you look at the properties of the Aggregate operator, under Defined Values, you’ll find that SQL Server constructed CASE expressions behind the scenes: … [Expr1022] = Scalar Operator(SUM(CASE WHEN [InsideTSQL2008].[Sales].[Orders].[shipcity]=N'Barcelona' THEN [InsideTSQL2008].[Sales].[Orders].[freight] ELSE NULL END)) … With this in mind, you shouldn’t expect the solution that’s based on the PIVOT operator to perform better than the standard solution. The main benefit in the PIVOT operator at the moment is that it’s less verbose. For more advanced pivoting requirements that the (non-standard) PIVOT syntax does not directly support, workarounds are needed. These may or may not lead to worse performance compared with CASE, depending on various factors including the skill level of the implementor. Examples of these problematic cases are covered in Itzik's article, and also well explained in Robert Sheldon's Simple Talk article, Questions About Pivoting Data in SQL Server You Were Too Shy to Ask. My experience has been that PIVOT and Agg(CASE... generate extremely similar plans with extremely close performance characteristics when both are written optimally. My usual advice is to write queries using whatever syntax feels most natural to you, and to only try rewrites if performance is not acceptable. Internals The SQL Server query processor does have a built-in Pivot logical operator (LogOp_Pivot), so it is maybe not quite correct to say that SQL Server rewrites pivots to aggregates and case expressions, at least if we are talking about parsing and compilation activities that take place prior to cost-based optimization (trivial plans are not available for pivot queries). On the other hand, it is true that the only way the optimizer can implement a query tree containing LogOp_Pivot is via the exploration rule ExpandPivot. This rule expands LogOp_Pivot into a normal grouped aggregate (LogOp_GbAgg) with associated scalar expressions. When this rule is disabled, pivot queries fail to compile. In practice then, we can say that pivots are always (eventually) 'rewritten' as aggregates and scalar expressions before an executable plan can be produced. Anyway, the result of the rewrite to LogOp_GbAgg is converted to the physical operators needed for an executable plan by the regular group-by aggregate implementation rules GbAggToHS (hash) or GbAggToStrm (stream). As a side note, the reason 'manual pivots' (aggregates on case expressions) have an extra Compute Scalar below the aggregate is that the case expressions are pushed toward the leaf level of the query tree during Project Normalization (an early stage of compilation, before cost-based optimization). Queries that use the PIVOT syntax do not have this because the expressions are not created until ExpandPivot runs during cost-based optimization. At the (earlier) time Project Normalization runs, the query tree still has LogOp_Pivot elements, so there are no projections to push down, and the case expressions typically end up inside the hash or stream aggregate. There is typically no advantage in avoiding the Compute Scalar, since from SQL Server 2005 onward, expression evaluation is normally deferred until the result is required by a later operator. In this case, evaluation of the case expressions is deferred until the aggregate (hash or stream) requires it.
Is my database ERD well designed
SQL, Database Testing • postgresql performance erd schema • • Marcee

2

0
Votes

2
Posts

0
Views

N

It depends on the queries you will run. Primary keys should always be bigint, not integer, and I'd use text rather than varchar(50).
O

Postgres_fdw LEFT JOIN with foreign table slows query down massively
SQL, Database Testing • performance query performance postgresql 9.4 postgresql fdw • • Oba22

2

0
Votes

2
Posts

0
Views

Y

Use pev to see how slow. EXPLAIN (ANALYZE, COSTS, VERBOSE, BUFFERS, FORMAT JSON) It's it network latency ?
Z

Modest sized DB with 320 second SELECT
SQL, Database Testing • performance postgresql performanc lan • • zymir

2

0
Votes

2
Posts

0
Views

Statistics were not up-to-date; executed ANALYZE and dropped the redundant: + " o.consumer IS NOT NULL " which improved performance 100x.

Should we create new tables instead of filtering 1 large table?

Suggested Topics

Join 4 tables and combine 1 table multiple row into one based on a key value SQL, Database Testing • mysql join • • Analeea

Estimated plan generation succeeds for batch creating and using #temp table but fails with a permanent table SQL, Database Testing • sql server exec languageution plan • • kalena

Speeding up SELECT FROM a large table SQL, Database Testing • mysql performance select innodb mariadb 10.5 • • Jolied

Not able to create this table SQL, Database Testing • mysql mysql 5.7 ddl • • Demir

How to most efficiently find out if a record has child records? SQL, Database Testing • postgresql performance performanc languagee tuni • • charlenef

Concatenate 2-3 table field values in 1 table field in access SQL, Database Testing • ms access c languageonc languageat • • Jolied

Maximum number of TEXT/BLOB columns per table/performance of table with many of them? SQL, Database Testing • mysql performance database design mysql 5.6 sc languagehema • • Trenton

why should I join 2 tables with 1 table apart SQL, Database Testing • mysql join • • Bogopo

Should I only have 1 table "User" Instead of 2 "Client" and "Worker" SQL, Database Testing • mysql database design • • juvenalb

Postgres Order By but only the most recent of a particular type SQL, Database Testing • postgresql performance query performance group by order by • • yajahira

JMeter: Getting error in non GUI mode Automated Testing • java performance jmeter load testing • • jules

Which is faster, SUM(CASE) or CTE PIVOT? SQL, Database Testing • sql server performance query performance pivot • • kalena

Is my database ERD well designed SQL, Database Testing • postgresql performance erd schema • • Marcee

Postgres_fdw LEFT JOIN with foreign table slows query down massively SQL, Database Testing • performance query performance postgresql 9.4 postgresql fdw • • Oba22

Modest sized DB with 320 second SELECT SQL, Database Testing • performance postgresql performanc lan • • zymir

Join 4 tables and combine 1 table multiple row into one based on a key value
SQL, Database Testing • mysql join • • Analeea

Estimated plan generation succeeds for batch creating and using #temp table but fails with a permanent table
SQL, Database Testing • sql server exec languageution plan • • kalena

Speeding up SELECT FROM a large table
SQL, Database Testing • mysql performance select innodb mariadb 10.5 • • Jolied

Not able to create this table
SQL, Database Testing • mysql mysql 5.7 ddl • • Demir

How to most efficiently find out if a record has child records?
SQL, Database Testing • postgresql performance performanc languagee tuni • • charlenef

Concatenate 2-3 table field values in 1 table field in access
SQL, Database Testing • ms access c languageonc languageat • • Jolied

Maximum number of TEXT/BLOB columns per table/performance of table with many of them?
SQL, Database Testing • mysql performance database design mysql 5.6 sc languagehema • • Trenton

why should I join 2 tables with 1 table apart
SQL, Database Testing • mysql join • • Bogopo

Should I only have 1 table "User" Instead of 2 "Client" and "Worker"
SQL, Database Testing • mysql database design • • juvenalb

Postgres Order By but only the most recent of a particular type
SQL, Database Testing • postgresql performance query performance group by order by • • yajahira

JMeter: Getting error in non GUI mode
Automated Testing • java performance jmeter load testing • • jules

Which is faster, SUM(CASE) or CTE PIVOT?
SQL, Database Testing • sql server performance query performance pivot • • kalena

Is my database ERD well designed
SQL, Database Testing • postgresql performance erd schema • • Marcee

Postgres_fdw LEFT JOIN with foreign table slows query down massively
SQL, Database Testing • performance query performance postgresql 9.4 postgresql fdw • • Oba22

Modest sized DB with 320 second SELECT
SQL, Database Testing • performance postgresql performanc lan • • zymir